CODEWITHSUNDEEP
excelvba
excelguy
excelguy

Reputation: 1624

VBA, Fill up/down rows to align with last row

I am trying to delete rows till it meets my last row OR fill down rows to align with last row.

Sometimes my sheet will be like below where I need to delete rows to align with my last row number.:

enter image description here

However sometimes my sheet will be like below, where I need to fill down other columns:

enter image description here

Is there a function that can do this? I am finding it hard to determine when to fill up or fill down.

Thanks

Upvotes: 2

Views: 369

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Please test the next code. It assumes that the reference column will be the seventh one and the one to check the 0 formulas value to be the sixth one. Your picture does not contain the columns header...

Sub DeleteRowsOrFillDownDiscontinuous()
 Dim sh As Worksheet, lastR As Long, lastR1 As Long, lastCol As Long

 Set sh = ActiveSheet
 lastR = sh.Range("F" & rows.count).End(xlUp).row
 lastR1 = Range("E" & rows.count).End(xlUp).row
 lastCol = sh.cells(lastR1, Columns.count).End(xlToLeft).Column

 If lastR < lastR1 Then
    sh.rows(lastR + 1 & ":" & lastR1).EntireRow.Delete xlUp
 ElseIf lastR > lastR1 Then
    sh.Range("A" & lastR1, "E" & lastR1).AutoFill _
        Destination:=sh.Range("A" & lastR1, sh.Range("E" & lastR))
    sh.Range("G" & lastR1, "AG" & lastR1).AutoFill _
       Destination:=sh.Range("G" & lastR1, "AG" & lastR)
    sh.Range("AI" & lastR1, sh.cells(lastR1, lastCol)).AutoFill _
       Destination:=sh.Range("AI" & lastR1, sh.cells(lastR, lastCol))
 Else
    MsgBox "Nothing te be processed. Everything aligned..."
 End If
End Sub

Edited:

Adapted the code for F:F column as reference, AH:AH not changeable, too and existing columns to be processed after AH Column.

Please test it and send some feedback.

Upvotes: 1

user3598756
user3598756

Reputation: 29421

keeping in mind the well known caveats of the use of UsedRange, you could give it a try

Dim lastRow As Long
With ActiveSheet ' <- change it to your actual sheet reference
    With .UsedRange
        lastRow = .Rows(.Rows.Count)
    End With
End With

Upvotes: 2

Related Questions