Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-12-2022, 01:28 AM
Marcia's Avatar
Marcia Marcia is offline Select until last row Windows 10 Select until last row Office 2019
Expert
Select until last row
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Select until last row

Hi. This code selects the range A5 to AQ50. I would like the code to select only the rows from A5 until the last row of a dynamic pivot table.
Code:
Dim lr As Long
    Range("A5:AQ5" & lr).Select
    Selection.Copy
What should the line be?


Thank you.
Reply With Quote
  #2  
Old 08-12-2022, 04:05 AM
p45cal's Avatar
p45cal p45cal is online now Select until last row Windows 10 Select until last row Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

We need to know a bit more about the pivot table.
Best attach a (mockup of) the workbook with what you want selected highlighted, also a bit of info on how dynamic the pivot will be (row numbers, column numbers, compact view perhaps etc.)
Reply With Quote
  #3  
Old 08-12-2022, 08:09 AM
Marcia's Avatar
Marcia Marcia is offline Select until last row Windows 10 Select until last row Office 2019
Expert
Select until last row
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Here is a mock up data.
Attached Files
File Type: xlsm Selec Last Row vba.xlsm (331.1 KB, 5 views)
Reply With Quote
  #4  
Old 08-12-2022, 10:48 AM
p45cal's Avatar
p45cal p45cal is online now Select until last row Windows 10 Select until last row Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

In the attached a macro:
Code:
Sub test()
Set PT = Sheets("Sum").PivotTables(1)
PT.TableRange1.Copy
Sheets("SumA").Range("F4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub
Separately, a copy of your pivot table at cell B15 of the SumA sheet with a small adjustment to how things are displayed.
Attached Files
File Type: xlsm msofficeforums49492Selec Last Row vba.xlsm (334.1 KB, 3 views)
Reply With Quote
  #5  
Old 08-12-2022, 03:54 PM
Marcia's Avatar
Marcia Marcia is offline Select until last row Windows 10 Select until last row Office 2019
Expert
Select until last row
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

It's perfect p45Cal
I did not know that the field lists in the Values section could also be the field lists in the Rows section. I thought that a field should only be placed in either Filter, Row or Column.
Thank you..
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select entire text and then de-select certain Headings? tq94 Word 3 02-26-2022 03:47 AM
Select until last row select paragraphs yacov Word 2 10-25-2020 02:53 AM
Select until last row How to select the end of a document? ArtKilp Word VBA 2 06-15-2020 06:44 PM
Select until last row Select a row in a different tab cosmicyes Excel Programming 1 07-09-2018 02:47 AM
Select until last row How to select row aymanharake Excel Programming 3 02-18-2017 04:09 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:20 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft