VBA : Select end of data range

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
Hi,

When pressing Ctrl + END you are taken to the end of the current data range.

I have a spreadsheet with a macro that removes all blanks, reducing 70 rows down to 30 rows. When i press Ctrl+END it still takes me to row 70 although all that data has now been deleted. How can i set the end of data range...(want to apply a formula in column A and have it copy down to the end of range which in practice is row 30, but in reality started as being row 70)

Tany thanks

T
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Ideally would but cant as it is part of a larger macro...

Import file and paste
Strip blank rows
Paste formula from A1 to bottom of range
Colour cells

Maybe could set up a variable that contains the row number of the last cell containing data then have formula copy from range("A1, EndOfRangeValue)

??

Regards

T
 
Upvote 0
Yes there are other ways to identify the end of the data: for example, assuming you know that in the last row of data the B column cell would definitely contain some data (and not be blank) you can determine what this last row is with:

Code:
Dim lngLastRow As Long
 
lngLastRow = Cells(Rows.Count,"B").End(xlUp).Row

Then you can use this to determine how far down you want your formulas copied:

Code:
Range("A1").Copy Destination:=Range("A2:A" & lngLastRow)

Make sense?
 
Upvote 0
Fantastic...

This will save me alot of hassle

Still quite rusty on the VBA coming from pure VB which haven't used for 4 years!

Many thanks

T
 
Upvote 0
Quick question...

Only works when i specifically state the sheet it is in :

lngLastRow = Sheets("PITA Report").Cells(Rows.Count, "A").End(xlUp).Row

Any thoughts?

Although I have renamed the Sheet to PITA Report...I would like to refer to it as its original document name (is that what you call it) of Sheet2!

Seen this done but cant remember how...would make the sheet more robust so that if name changes occur, it is still referred to as Sheet2 object name

?

T
 
Upvote 0
Hi

I believe you mean the sheet's code name which you can see if you go to the VBE and click on the sheet name in the top left Project Window. The Properties window should open up in the bottom left - the name at the top is the codename of the sheet (alternatively, with the sheet active, type the following in to the Immediate Window in the VBE and hit Enter:

?Activesheet.CodeName

If you use that, then you are correct that subsequent renamings shouldn't cause problems (as long as your workbook is active when the code is run).
 
Upvote 0
Can you please provide this as a practical example in code as doesnt seem to work e.g.

referring to cell A1 in sheet 5 of workbook MainSheet (variable containing name of current workbook)

Regards

T
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top