I'd like to select all the rows and columns in a spreadsheet. The macro needs to be dynamic, as the number of columns and rows tend to vary each time the macro would be called. It also needs to be able to account for blank rows and columns.
This subroutine accomplishes part of the process:
Sub FindLastCell()
Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Select
End Sub
It finds and selects the very last cell in a spreadsheet. Now that I've found the very last cell in the spreadsheet, how do I select cell A1 to the LastCell as a range?
Best Answer
You need to make these mods to the code
Select
withFind
as this will give an error if the Find returns nothing. Instead test that the range objectIs Not Nothing
Find
can search by row and by column. You need to determine both last row and column to determine the true last used cellRange
to set a range from the first cell (A1) to your cell determined with the twoFind
rangesPls see the code below
If the
Find
gets a value then it makes a rangerng3
from A1 to the last used cell identified by the twoFind
s.