Excel - Enter Numbers in a column

New Contributor

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.

SAckerman_3-1610481289972.png

 

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

Go to the Next sheet

SAckerman_4-1610481369396.png

 

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:

SAckerman_5-1610481465874.png

 

Here is the current macro:

 
SAckerman_6-1610481534325.png

 

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

2 Replies
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

@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