bobrossi56
asked on
VBA in Excel to position cursor in the last row & column cell
I have a worksheet in Excel 2010 that contains lots of data. I need the VBA code that will position the cursor in the last row and column cell. Another macro I run requires the cursor to be in the last cell and finding it manually every time before I execute the macro is undesirable. I will add the code you suggest to the beginning of the macro and eliminate the manual work. In case it matters this is the macro I run now that requires me to manually place the cursor in the last cell and then drag the range of the sheet:
---------------
Sub clean()
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Drag
DeleteStr = Application.InputBox("Clic
For Each rng In InputRng
If rng.Value = DeleteStr Then
If DeleteRng Is Nothing Then
Set DeleteRng = rng
Else
Set DeleteRng = Application.Union(DeleteRn
End If
End If
Next
DeleteRng.EntireRow.Delete
End Sub
----------------------
thx experts
BobR
---------------
Sub clean()
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Drag
DeleteStr = Application.InputBox("Clic
For Each rng In InputRng
If rng.Value = DeleteStr Then
If DeleteRng Is Nothing Then
Set DeleteRng = rng
Else
Set DeleteRng = Application.Union(DeleteRn
End If
End If
Next
DeleteRng.EntireRow.Delete
End Sub
----------------------
thx experts
BobR
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect
Please note, the code relies on the UsedRange variable being valid.
It has been known, not infrequently, for this variable to become invalid by over us of formatting, eg highlighting whole rows rather than selected cells.
It may be better to use an automated navigation such as you would have done manually.
For example, go to the very last row possible 1048576 in a column that you know will be populated and then come up to the last entry. Then in that row go to the very last column possible XFD and then come left.
Both moves are reliant on the whole row and/or column being populated.
It has been known, not infrequently, for this variable to become invalid by over us of formatting, eg highlighting whole rows rather than selected cells.
It may be better to use an automated navigation such as you would have done manually.
For example, go to the very last row possible 1048576 in a column that you know will be populated and then come up to the last entry. Then in that row go to the very last column possible XFD and then come left.
Both moves are reliant on the whole row and/or column being populated.
ASKER