Excel - Enter Numbers in a column

%3CLINGO-SUB%20id%3D%22lingo-sub-2054284%22%20slang%3D%22en-US%22%3EExcel%20-%20Enter%20Numbers%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2054284%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-message-subject-wrapper%20lia-component-subject%20lia-component-message-view-widget-subject-with-options%22%3E%3CDIV%20class%3D%22MessageSubject%22%3E%3CDIV%20class%3D%22MessageSubjectIcons%20%22%3E%3CSPAN%20class%3D%22lia-message-read%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fmicrosoft-learn%2Fexcel-vba-macro-novice-correct-macro-number-rows-in-a-column%2Fm-p%2F2044187%22%20target%3D%22_blank%22%3EExcel%20VBA%20macro-Need%20help%20to%20correct%20macro-number%20rows%20in%20a%20column%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FA%3E%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3EI%20am%20a%20novice%20on%20this%20site%20and%20Excel%20VBA%20within%20macros.%3C%2FP%3E%3CP%3ETrying%20to%20finish%20the%20macro%20without%20errors.%20Using%20pick%20button%20to%20run%20the%20macro.%3C%2FP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SAckerman_3-1610481289972.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F245735iAF69B70A9CC75E96%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22SAckerman_3-1610481289972.png%22%20alt%3D%22SAckerman_3-1610481289972.png%22%20%2F%3E%3C%2FSPAN%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFill%20in%20the%20yellow%20column%20with%20quantity%2C%20math%20is%20done%20with%20columns%20B10*G10%3D%20in%20H10%3C%2FP%3E%3CP%3EGo%20to%20the%20Next%20sheet%3C%2FP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SAckerman_4-1610481369396.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F245737iFA0BB0FD991DF33C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22SAckerman_4-1610481369396.png%22%20alt%3D%22SAckerman_4-1610481369396.png%22%20%2F%3E%3C%2FSPAN%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMove%20all%20rows%20which%20have%20B%20column%20greater%20than%200%20-%20all%20works%20fine%3C%2FP%3E%3CP%3EStart%20of%20macro%20works%20fine%20-%20remove%20all%20blank%20rows%20if%20Column%20B%20is%20blank.%3C%2FP%3E%3CP%3EThe%20problem%20is%20to%20renumber%20column%20A%20rows%20after%20all%20blank%20rows%20are%20deleted.%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20want%20the%20result%20to%20be%3A%3C%2FP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SAckerman_5-1610481465874.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F245738i930EE8357CA23992%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22SAckerman_5-1610481465874.png%22%20alt%3D%22SAckerman_5-1610481465874.png%22%20%2F%3E%3C%2FSPAN%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20current%20macro%3A%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SAckerman_6-1610481534325.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F245739i1BEDB1E2F28AFD59%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22SAckerman_6-1610481534325.png%22%20alt%3D%22SAckerman_6-1610481534325.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20Button1_Click()%0A%0ADim%20LastRow%20As%20Long%2C%20Firstrow%20As%20Long%0ADim%20r%20As%20Long%0A%0AWith%20ActiveSheet%0A%20%20%20%20Firstrow%20%3D%2010%0A%20%20%20%20LastRow%20%3D%20.UsedRange.Rows(.UsedRange.Rows.Count).Row%0A%20%20%20%20%0A%20%20%20%20For%20r%20%3D%20LastRow%20To%20Firstrow%20Step%20-1%0A%20%20%20%20%20%20%20%20If%20.Range(%22B%22%20%26amp%3B%20r).Value%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20.Range(%22B%22%20%26amp%3B%20r).EntireRow.Delete%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20Next%20r%0A%20%20%20%20%0AEnd%20With%0A%0A'These%20attempts%20give%20me%20errors%20or%20just%20doesn't%20work%0A'___________________________%0A'With%20Range(%22A10%3AA%22%20%26amp%3B%20Cells(Rows.Count%2C%20%22B%22).End(x1UP).Row)%0A'%20%20%20%20.Value%20%3D%20Evaluate(%22Row(%22%20%26amp%3B%20.Address%20%26amp%3B%20%22)%22)%0A'End%20With%0A'___________________________%0A'Range(%22A10%3AA%22%20%26amp%3B%20Range(%22B%22%20%26amp%3B%20Rows.Count).End(x1Up).Row).DataSeries%20%2C%20x1Linear%0A'___________________________%0A'Range(%22A10).AutoFill%20Range(%22A10%3AA%22%20%26amp%3B%20Range(%22B%22%20%26amp%3B%20Rows.Count).End(x1Up.Row)%2C%20x1FillSeries%0A%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan't%20seem%20to%20paste.%20Will%20attach%20file.%3C%2FP%3E%3CP%3ENote%3A%20Excel%20version%202010%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3Efrom%20Frustrated%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2054284%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2061265%22%20slang%3D%22en-US%22%3ERE%3A%20Excel%20-%20Enter%20Numbers%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061265%22%20slang%3D%22en-US%22%3Ethis%20is%20the%20community%20dedicated%20to%20all%20things%20training%20and%20certification%20related%20to%20Microsoft%20Learn.%20Your%20question%20may%20be%20better%20answered%20by%20visiting%20the%20Excel%20Community%20-%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fbd-p%2FExcelGeneral%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fbd-p%2FExcelGeneral%3C%2FA%3E%3C%2FLINGO-BODY%3E
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