Auto fill down cell in Excel VBA Macro

Question

Sub AutoFill()

    Dim x As Long
    Dim y As Long
    Dim lastrow As Long
    Dim lastcolumn As Long

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    lastcolumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
    lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

    For x = 2 To lastrow
        If Cells(x, 2).Value = "" Then
            Cells(x, 2).Value = Cells(x - 1, 2).Value
            Cells(x, 3).Value = Cells(x - 1, 3).Value
            Cells(x, 5).Value = Cells(x - 1, 5).Value
        End If
    Next x

    Application.ScreenUpdating = True

End Sub

With the above code My cells are being filled up, but the last row fills till the end of excel sheet. In the Excel sheet column D is already filled in Column B C & E should be auto fill to down. What should be the changes in the code?

why ` ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count` ??stackoverflow.com/questions/71180/… — cyboashu
– cyboashu, Commented Jul 20, 2016 at 17:45
Your code is working as per your requirement on my pc. Check — skkakkar
– skkakkar, Commented Jul 20, 2016 at 17:53
For lastRow, I'd instead compare your three columns, and get the last row from there. UsedRange, in my experience, doesn't always use the range I'd expect. lastRow = Max(cells(rows.count,2).End(xlUp).Row,cells(rows.count,3).End(xlUp).Row,cells(rows.count,5).End(xlUp).Row or LastRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row — BruceWayne
– BruceWayne, Commented Jul 20, 2016 at 17:57
yes, I have changed it to LastRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row still there are some problems, in column E there are some parts are not filled in with the macro — Jyotirmaya Chandra
– Jyotirmaya Chandra, Commented Jul 20, 2016 at 18:18

user6432984 · Accepted Answer · 2016-07-20 19:45:08Z

1

Excel VBA Last Row: The Complete Tutorial To Finding The Last Row In Excel With VBA (And Code Examples) recommends using LookIn:=xlFormulas when determining the last with using Cells.Find.

lastrow = Find(What:=” * ”, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Because you stated that column D is already filled in I use:

lastrow = Range("D" &  Rows.Count).End(xlUp).Row

If column E isn't filled in then Cells(x, 2).Value must be <> "".

Sub AutoFill()
    Dim x As Long
    Dim y As Long
    Dim lastrow As Long
    Dim lastcolumn As Long
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    lastcolumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
    lastrow = Range("D" &  Rows.Count).End(xlUp).Row

    For x = 2 To lastrow

        If Cells(x, 2).Value = "" Then Cells(x, 2).Value = Cells(x - 1, 2).Value
        If Cells(x, 3).Value = "" Then Cells(x, 3).Value = Cells(x - 1, 3).Value
        If Cells(x, 5).Value = "" Then Cells(x, 5).Value = Cells(x - 1, 4).Value

    Next x
    Application.ScreenUpdating = True

End Sub

answered Jul 20, 2016 at 19:45

user6432984

Sign up to request clarification or add additional context in comments.

1 Comment

user6432984 Over a year ago

Awesome! Glad that I could help.

Collectives™ on Stack Overflow

Auto fill down cell in Excel VBA Macro

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related