Select a range of cells up to last row that contains data

[t.powley]

Hi,

Can anyone help, I want to be able to use VBA to select columns A to D down to the last row that contains any data. The data could be in any row and in any column from A to D

For example:
if the last cell to contain data is B10, I would like the range to be A1:D10
if the last cell to contain data is C57, I would like the range to be A1:D57

Thank you in advanced.

 

[VoG]

Try like this

Sub atest()
Dim LR As Long
LR = Columnns("A:D").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("A1:D" & LR).Select
End Sub

 

[t.powley]

wow! Thanks for the quick reply.

Works perfectly apart from a slight spelling mistake (Columnns) but seeing as you helped me out so quickly I will look past that one... lol

Cheers


working version

Sub atest()
Dim LR As Long
LR = Columns("A:D").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("A1:D" & LR).Select
End Sub

 

[Rick Rothstein]

If your data is always constants (that is, not formulas), then Peter's code is fine. However, if you have formulas in some of your columns, and if those formulas might end up displaying the empty string ("") under certain conditions, then Peter's code might give you the wrong result depending on the settings you used the last time you performed a Find operation (Find remembers its setting whether set manually or via code). Adding the LookIn argument and setting it depending on your data can be used to force the outcome.

For last displayed value even if that value is from a formula; however formulas displaying the empty string ("") are ignored
-------------------------------------------------------------------------------------------------------------------------------
LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

For last row containing either a value or formula even if that formula is displaying the empty string ("")
-------------------------------------------------------------------------------------------------------------------------------
LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row