asked on

VBA autofill blank cells in a column

Euro5 · 2023-08-24T12:57:25Z

VBA - I am trying to do a simple auto fill but can't figure it out. The sheet has some data in column BF (always different row). I need to fill the column BF, where there are blanks, with 0 all the way down. BG has the count for the LAST row I need to fill to. So I am trying to get the first empty cell in BF and fill the empty cells down to the last populated row in BG. Can anyone help!?? Sheets("Sheet1").Select Range("BF2").Select Selection.End(xlDown).Select ActiveCell.Offset(1).Select ActiveCell.FormulaR1C1 = "0" LastRowA = Range("BF1048576").End(xlUp).Row LastRowB = Range("BG" & Rows.Count).End(xlUp).Row Range("BF" & LastRowA + 1).AutoFill Destination:=Range("BF" & LastRowA + 1 & ":" & "BF" & LastRowB), Type:=xlFillDefault

VBA - I am trying to do a simple auto fill but can't figure it out.

The sheet has some data in column BF (always different row).

I need to fill the column BF, where there are blanks, with 0 all the way down.

BG has the count for the LAST row I need to fill to.

So I am trying to get the first empty cell in BF and fill the empty cells down to the last populated row in BG.
Can anyone help!??

Sheets("Sheet1").Select

Range("BF2").Select

Selection.End(xlDown).Select

ActiveCell.Offset(1).Select

ActiveCell.FormulaR1C1 = "0"

LastRowA = Range("BF1048576").End(xlUp).Row

LastRowB = Range("BG" & Rows.Count).End(xlUp).Row

Range("BF" & LastRowA + 1).AutoFill Destination:=Range("BF" & LastRowA + 1 & ":" & "BF" & LastRowB), Type:=xlFillDefault

ASKER CERTIFIED SOLUTION

Robberbaron (robr)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Ashraf El Desoky

It looks like you're trying to automatically fill the cells in column BF (starting from the first empty cell) with "0" down to the last populated row specified in column BG. However, the code you provided seems a bit convoluted and might not work as expected. Here's a more streamlined version of the code that should accomplish what you're trying to achieve:

vbaCopy codeSub FillColumnBF()
    Dim ws As Worksheet
    Dim lastRowA As Long
    Dim lastRowB As Long
    
    ' Set a reference to the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Find the last populated row in column A (BF)
    lastRowA = ws.Cells(ws.Rows.Count, "BF").End(xlUp).Row
    
    ' Find the last populated row in column B (BG)
    lastRowB = ws.Cells(ws.Rows.Count, "BG").End(xlUp).Row
    
    ' Fill column BF with "0" from the first empty cell down to the last populated row in column BG
    ws.Range("BF" & (lastRowA + 1) & ":BF" & lastRowB).Value = "0"
End Sub

Open in new window

This code directly fills the range in column BF with "0" from the first empty cell after the last populated cell in column BF down to the last populated cell in column BG. It avoids unnecessary selection and offset operations, making it cleaner and more efficient.

Make sure to replace "Sheet1" with the actual name of your sheet if it's different. Also, remember to back up your workbook before running any VBA code.

Euro5

ASKER

These are not working and I figured out why.
When I run and look at the code value, because it is a table, it counts ALL the rows in the table rather than just that column.