Excel using code 2

[darinmc]

I have a spreadsheet, 1 column is name InvNo, I was wondering if there was code to automatically fill in the blanks..
Eg: Col appears like this...
1
2


3


4
5
Etc, I would like the number after 2, to be filled with 2's, then after 3, filled with 3's..
I know you can use the mouse and drag it, BUT my numbers run to around 170, the task is very VERY monotomous, boring and time consuming?
is this possible?

Thx
Darin

 

[missinglinq]

You've posted this in the Access Modules (VBA Coding) forum! You'll have a better chance with an answer reposting to the VBA: Visual Basic for Applications forum here!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Ed2020]

My Excel VBA isn't very good so there may be a much better way of doing this but the following works:

Public Sub FillBlanks()
    Dim lngLastKnownVal As Long
    Dim pntr As Long
    Dim lngLastRow As Long
    
    For pntr = 1 To 65536
        If Sheet1.Cells(pntr, 1) <> "" Then lngLastRow = pntr
    Next pntr
        
    For pntr = 1 To lngLastRow
        If Sheet1.Cells(pntr, 1) <> "" Then
            lngLastKnownVal = Sheet1.Cells(pntr, 1)
        Else
            Sheet1.Cells(pntr, 1) = lngLastKnownVal
        End If
    Next pntr
End Sub

Ed Metcalfe.

Please do not feed the trolls.....

 

[darinmc]

Thx, Never used VBA in excel, got it to work, EXCELLENT, thx Ed

 

[Ed2020]

You're welcome.

Ed Metcalfe.

Please do not feed the trolls.....

 

[SkipVought]

Hi,

Public Sub FillBlanks()
    Dim lngLastKnownVal As Long
    Dim pntr As Long
    Dim lngLastRow As Long
    
    [b]lngLastRow  = cells.find("*", cells(cells.rows.count,cells.columns.count) , , , xlByRows , xlPrevious).row[/b]
        
    For pntr = 1 To lngLastRow
        If Sheet1.Cells(pntr, 1) <> "" Then
            lngLastKnownVal = Sheet1.Cells(pntr, 1)
        Else
            Sheet1.Cells(pntr, 1) = lngLastKnownVal
        End If
    Next pntr
End Sub

Skip,

_[red][/red]

 

[Ed2020]

Yep, that was the bit I was thinking could be done better!

Ed Metcalfe.

Please do not feed the trolls.....

 

[darinmc]

Thx To both.... even bettttttter lol

saves even more time...
Darin

 

[TheAceMan1]

darinmc . . .

If the help from [blue]Ed2020[/blue] & [blue]SkipVought[/blue] was that good, give them a star! . . . its the best way to show your appreciation and it signals other members of resolution!

See Ya! . . . . . .