Friday 26 August 2016

excel - Return a range from A1 to the true last used cell



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?


Answer



You need to make these mods to the code





  1. The sheet may be blank, so you should never use Select with Find as this will give an error if the Find returns nothing. Instead test that the range object Is Not Nothing

  2. Find can search by row and by column. You need to determine both last row and column to determine the true last used cell

  3. Once you have determined the true last cell use Range to set a range from the first cell (A1) to your cell determined with the two Find ranges



Pls see the code below



If the Find gets a value then it makes a range rng3 from A1 to the last used cell identified by the two Finds.



enter image description here




Sub GetRange()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Set rng1 = Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious)
Set rng2 = Cells.Find("*", [a1], xlFormulas, , xlByColumns, xlPrevious)
If Not rng1 Is Nothing Then
Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column))
MsgBox "Range is " & rng3.Address(0, 0)

'if you need to actual select the range (which is rare in VBA)
Application.Goto rng3
Else
MsgBox "sheet is blank", vbCritical
End If
End Sub

No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...