Excel - Enter Numbers in a column

[SAckerman]

Excel VBA macro-Need help to correct macro-number rows in a column  

I am a novice on this site and Excel VBA within macros.

Trying to finish the macro without errors. Using pick button to run the macro.

Fill in the yellow column with quantity, math is done with columns B10*G10= in H10

Go to the Next sheet

Move all rows which have B column greater than 0 - all works fine

Start of macro works fine - remove all blank rows if Column B is blank.

The problem is to renumber column A rows after all blank rows are deleted.

This is what I want the result to be:

Here is the current macro:

Sub Button1_Click()

Dim LastRow As Long, Firstrow As Long
Dim r As Long

With ActiveSheet
    Firstrow = 10
    LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    
    For r = LastRow To Firstrow Step -1
        If .Range("B" & r).Value = "" Then
           .Range("B" & r).EntireRow.Delete
        End If
        
    Next r
    
End With

'These attempts give me errors or just doesn't work
'___________________________
'With Range("A10:A" & Cells(Rows.Count, "B").End(x1UP).Row)
'    .Value = Evaluate("Row(" & .Address & ")")
'End With
'___________________________
'Range("A10:A" & Range("B" & Rows.Count).End(x1Up).Row).DataSeries , x1Linear
'___________________________
'Range("A10).AutoFill Range("A10:A" & Range("B" & Rows.Count).End(x1Up.Row), x1FillSeries


End Sub

Can't seem to paste. Will attach file.

Note: Excel version 2010

Thanks in advance,

from Frustrated

[SAckerman]

I am a novice on this site and Excel VBA within macros.

Trying to finish the macro without errors. Using pick button to run the macro.

Fill in the yellow column with quantity, math is done with columns B10*G10= in H10

Go to the Next sheet

Move all rows which have B column greater than 0 - all works fine

Start of macro works fine - remove all blank rows if Column B is blank.

The problem is to renumber column A rows after all blank rows are deleted.

This is what I want the result to be:

Here is the current macro:

[#3]

this is the community dedicated to all things training and certification related to Microsoft Learn. Your question may be better answered by visiting the Excel Community - https://techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral

[#4]

@SAckerman ,

you can check this:

Sub Button1_Click()

Dim LastRow As Long, Firstrow As Long
Dim r As Long
Dim c As Integer
With ActiveSheet
Firstrow = 10
'counter - how many rows you have
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

For r = LastRow To Firstrow Step -1
If .Range("B" & r).Value = "" Then
.Range("B" & r).EntireRow.Delete
'counter - how many rows were deleted
c = c + 1
End If

Next r

End With

Range("A10").Select
ActiveCell.Value = "1"
'use both variable to calculate new last row :)
Selection.AutoFill Destination:=Range(("A10:A" & LastRow - c)), Type:=xlFillSeries

End Sub