Numbering rows, or deleting where column is blank

hg1027

Board Regular
Joined
Apr 28, 2008
Messages
56
I have the following pieces of code

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?
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

cornflakegirl

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
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:
Code:
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

Board Regular
Joined
Apr 28, 2008
Messages
56
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...

Code:
    '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
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 28, 2008
Messages
56

ADVERTISEMENT

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).
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
... 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

Well-known Member
Joined
Nov 4, 2004
Messages
2,023
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top