View Single Post
Old 06-26-2018, 10:44 AM   #2
smiler44
Newly Registered User
 
Join Date: Jul 2008
Location: UK
Posts: 601
Thanks: 12
Thanked 9 Times in 8 Posts
smiler44 is on a distinguished road
Re: Help needed to modify the following macro

this will find the first blank cell in column A so you could use this if there are no gaps in column A to find the next blank cell/row

Sub Macro1()
Dim lst As String
Dim lr As String
ActiveSheet.Range("a2").Select ' row to start
If ActiveCell.Offset(1, 0) <> "" Then
Selection.End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
lst = ActiveCell.Address ' cell address of first blank cell
lr = Right(lst, Len(lst) - 3) ' just the number of the first blank cell
MsgBox lr
End Sub

or this macro will tell you the last row used

Private Sub CommandButton1_Click()
Dim ExcelLastCell As Object, lRow As Long, lLastDataRow As Long, l As Long
Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
lLastDataRow = ExcelLastCell.Row
lRow = ExcelLastCell.Row
Do While Application.CountA(ActiveSheet.Rows(lRow)) = 0 And lRow <> 1
lRow = lRow - 1
Loop
lLastDataRow = lRow
Sheets("sheet1").TextBox1.Text = lLastDataRow
End Sub
smiler44 is offline   Reply With Quote