Find last row of data in column A on a specific sheet, and after that copy data down to that row on another sheet.

exPERten

New Member
Joined
Jun 23, 2020
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
I am almost there I think.
But this seems to search for data in all columns on the first sheet. I only want to check column A on Sheet 1.

Sub FindLastRow()
Dim LastRow As Long
With Sheets("Sheet 1")
If WorksheetFunction.CountA(.Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = .Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
Range("B16:AS" & LastRow).FillDown
End With
End Sub
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub FindLastRow()
Dim LastRow As Long
With Sheets("Sheet 1")
If WorksheetFunction.CountA(.Range("A:A")) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = .Range("A:A").Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
Range("B16:AS" & LastRow).FillDown
End With
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,235
Messages
5,546,649
Members
410,752
Latest member
MC01_
Top