Numbering rows, or deleting where column is blank

[hg1027]

I have the following pieces of code

'Inserts new column before A
Range("A:A").Select
Selection.Insert Shift:=xlToRight
 
'Fills down to last row with record number
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "2"
Range("A2:A3").Select
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Selection.AutoFill Destination:=Range("A2").Resize(LastRow), Type:=xlFillDefault

which I have cobbled together with recorded macros and bits from here. I'm trying to go from this (one column)

NAME
Tom
****
Harry

to (2 columns)
# NAME
1 Tom
2 ****
3 Harry

with a varying range (sometimes it's 3 lines, sometimes it's 12 lines...).

The problem I'm having is that right now it adds 2 extra numbers at the bottom (only 1 if I just run those two pieces - strange - but I don't want ANY extras...). Why?

Am I using the LastRow function incorrectly? It seems like it will be very useful in creating the semi-idiot proof macro I'm working on now, but I think I'm missing something. Offsets maybe?

 

[cornflakegirl]

The problem is with Resize. You're telling it you want the size of the selection resized to be "LastRow" number of rows - but you actually only want "LastRow - 1" rows - because row 1 isn't in your selection.

BTW, you generally don't need to select in VBA (the recorder writes bad code!).

The start of your code can be achieved with:

Range("A1").EntireColumn.Insert
Range("A2").Value = 1
Range("A3").Value = 2

I like the way you've used AutoFill. I wouldn't have thought of that - I would have gone straight for a counter. I'm assuming AutoFill will be more efficient, although I haven't checked.

 

[hg1027]

Thanks

Thanks for the reply Emma. I won't show you the part where I renamed 17 columns, it's almost embarassing now that I read your post! I wish I could take credit for the Autofill, but I picked it up off here somewhere. I'll start making a note of who helped me out and who's code I used.

As far as the LastRow -n, turns out it's -n at the end of the line, as below. Works great now. And can I skip the selection part with Autofill? Seems like it might still be necessary...

    'Inserts new column and numbers the rows, Range format from cornflakegirl
    Range("A1").EntireColumn.Insert
    Range("A2").Value = 1
    Range("A3").Value = 2
    Range("A2:A3").Select
    LastRow= Range("B" & Rows.Count).End(xlUp).Row - 2
    Selection.AutoFill Destination:=Range("A2").Resize(LastRow), Type:=xlFillDefault

 

[Peter_SSs]

Re: Thanks

You could try this:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> AddNumbers()<br>    <SPAN style="color:#007F00">'Inserts new column before A</SPAN><br>    Columns(1).Insert<br>     <br>    <SPAN style="color:#007F00">'Fills down to last row with record number</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("B2", Range("B" & Rows.Count).End(xlUp)).Offset(, -1)<br>        .Cells(1).Value = 1<br>        .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[hg1027]

Thanks Peter, I'll try that out. I realize there are always going to be many ways to do the same thing, is there anything wrong with the way I have it? I'll definitely be keeping yours for inspiration.

For now I think I'll use it as it is because I can use the same format and insert whatever I want (text, formula, etc).

 

[Peter_SSs]

... is there anything wrong with the way I have it?

If it does what you want, then definitely not!

As you already pointed out, I was just showing another way of doing it.

 

[cornflakegirl]

hg - i just tested the relative speed of your way, peter's way, and using a counter - just out of curiosity. for around 20000 lines. peter's came out the same or slightly faster than yours - but we're talking hundredths of a second, so no noticeable difference. the counter took over a second.