autofill

[GordonLS]

Good morning, here is my problem. I am struggling with maybe the last line of missing code. The code below populates w2 and w3 with "1" and "2" respective. It then selects the range I need (to the last row). I need the sequential numbering. In this case it would be "1" thru "8". However I can't get it to fill the range. Any ideas would be appreciated


Range("w2").Select
ActiveCell.FormulaR1C1 = "1"
Range("w3").Select
ActiveCell.FormulaR1C1 = "2"
Range("I1").Select
Selection.End(xlDown).Select
lastRow = ActiveCell.Row
Range("w2:w3").select
ActiveCell.Offset(0, 0).Range("A1:A" & (lastRow - 1)).Select

 

[Joe4]

See if this does what you want:

Sub MyPopulateCounter()

    Dim lr As Long
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Populate column W with counter formula
    Range("W2:W" & lr).Formula = "=Row()-1"
'   Hard-code values
    Range("W2:W" & lr).Value = Range("W2:W" & lr).Value
    
End Sub

 

[GordonLS]

I will give it a try Joe. Can I do this without DIM?

 

[Joe4]

I will give it a try Joe. Can I do this without DIM?

You could, if you do not have "Option Explicit" turned on, but it is considered "best practice" to declare all your variables before using them.
Why would you NOT want to do that?

By turning on Option Explicit, which forces you to declare all variables before using them, it greatly helps in error debugging (and is generally recommended).
See here for details: Option Explicit in Excel VBA

 

[GordonLS]

I can declare. I am new to VBA and just trying to learn by trial. I have read about Option Explict. Thanks again for your help and quick response.

Gordon

 

[Joe4]

I can declare. I am new to VBA and just trying to learn by trial. I have read about Option Explict. Thanks again for your help and quick response.

If you are just learning, it is good to learn the best practices and get in that habit now.
What may seem like a minor inconvenience (having to declare each variable up front) can actually save you a lot of headaches down the road.
I cannot tell you how many times I have misspelled a variable, and using Option Explicit alerted me to it right away.

 

[GordonLS]

Thanks man.

 

[Joe4]

You are welcome.

So did the code work for you?

 

[GordonLS]

Joe, a little snag although I worked around it.
The file I have today has 22 rows. At the end of my macro I end up with 8 rows. The issue is that DIM LR as Long is picking up the 22 rows and populating all 22 in column "W" when I only want the 8 with data. My work around was to move the DIM statement from the beginning of the sub and place it just before the code you helped with earlier. Is this the correct way to work?

Gordon

 

[Joe4]

Joe, a little snag although I worked around it.
The file I have today has 22 rows. At the end of my macro I end up with 8 rows. The issue is that DIM LR as Long is picking up the 22 rows and populating all 22 in column "W" when I only want the 8 with data. My work around was to move the DIM statement from the beginning of the sub and place it just before the code you helped with earlier. Is this the correct way to work?

The location of the DIM statement has no affect on what value is being assigned to it (as long as it doesn't exceed the maximum possible value).
It is this calculation that determines that:

    lr = Cells(Rows.Count, "A").End(xlUp).Row

Can you post the version of the code that is working for you?

The real question is how we determine how many rows need to be populated.
In the code I provided, it is looking to see what is the last cell populated in column A.
Should we be looking at some other column to see where the end of the data actually is?
Or perhaps running it as a different point/time in the macro, if data is being added/removed in the macro.