Insert & Sequentially Number Column

[Guest]

Hi,
This is relatively simple, but I'm having a bit of trouble with it as I'm
not all that familiar with vba.

Objective:
Insert new column A, title it "Key", sequentially number from A2 to end of
data range in column a, i.e. 1, 2, 3, 4, etc.

Problem:
If column B contains empty cells, the numbering stops. I need it to keep
numbering until it truly reaches the end of the data range.

Thanks!

 

[Ingolf]

Hi,

instead of Cells(2, 1).End(xlDown)

use Cells(Rows.Count, 1).End(xlUp)

to refer to the last cell containing data in column A.

Regards,
Ingolf

 

[Mark Driscol]

A new column A will not contain any data. When you said "sequentially
number from A2 to end of data range in column a", did you mean
'sequentially number from A2 to end of data range in column b"? This
is, as long as there are non-blank cells in column B, put data into
column A? It isn't clear what data in column B have to do with what
you are asking. Sorry if I am missing something.

Mark

 

[Guest]

Hi,
I want the sequential numbering to appear in column A, which is newly
inserted. The problem arises when a cell in the neighbouring column (b)
doesn't contain any data, the numbering stops. For example, it will number
1, 2, 3, etc. down to, say row 10. It stops because in B10 there is an empty
cell. This is a problem, because the data doesn't really stop at b10. There
is data in B11, B12, etc.... just some blank cells here and there, but this
causes Excel to think it has reached the end of the range.

Basically, I'd love a code snippet that says:
1. Fill something down an entire column, truly to the end of the data, even
if you encounter a few blank cells in the column to the right (or left
sometimes)
2. Select down an entire column, truly to the end of the data, even if you
encounter a few blank cells in the column to the right (or left sometimes)

Hope this is more clear.
Thanks

--
Thanks!

Dee

A new column A will not contain any data. When you said "sequentially
number from A2 to end of data range in column a", did you mean
'sequentially number from A2 to end of data range in column b"? This
is, as long as there are non-blank cells in column B, put data into
column A? It isn't clear what data in column B have to do with what
you are asking. Sorry if I am missing something.

Mark

 

[Guest]

Hi,
If I record:
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Avnet Key"
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "2"
Range("A2:A3").Select

Where do I put your code?

Thanks

 

[Mark Driscol]

The following code answers 1). For 2), you can see maybe from the
below how to do it?

Option Explicit

Sub FillColumnA()

Dim i As Long
Dim lngLastRow As Long

' Insert new column
Range("A1").EntireColumn.Insert

' Reset last row of spreadsheet in case any rows
' were previously deleted, cells were cleared, etc.
lngLastRow = ActiveSheet.UsedRange.Rows.Count

' Find out last row of spreadsheet
lngLastRow = ActiveSheet.Range("A1") _
.SpecialCells(xlCellTypeLastCell).Row

Range("A1").Value = "Key"
For i = 2 To lngLastRow
Cells(i, "A").Value = i - 1
Next i

End Sub


Mark

Hi,
I want the sequential numbering to appear in column A, which is newly
inserted. The problem arises when a cell in the neighbouring column (b)
doesn't contain any data, the numbering stops. For example, it will number
1, 2, 3, etc. down to, say row 10. It stops because in B10 there is an empty
cell. This is a problem, because the data doesn't really stop at b10. There
is data in B11, B12, etc.... just some blank cells here and there, but this
causes Excel to think it has reached the end of the range.

Basically, I'd love a code snippet that says:
1. Fill something down an entire column, truly to the end of the data, even
if you encounter a few blank cells in the column to the right (or left
sometimes)
2. Select down an entire column, truly to the end of the data, even if you
encounter a few blank cells in the column to the right (or left sometimes)

Hope this is more clear.
Thanks

 

[ben77]

Give this a go:


Code:
--------------------
Sub Macro1()
Dim intLastrow

Columns(1).Insert Shift:=xlToRight
Cells(1, 1) = "Key"
Cells(2, 1) = "1"
intLastrow = Cells(65536, 2).End(xlUp).Row
For r = 3 To intLastrow
Cells(r, 1) = Cells(r - 1, 1) + 1
Next r

End Sub
--------------------


Hope this helps,

B

 

[Guest]

Thank you ben! It worked beautifully.

 

[Guest]

Thank you Mark. It worked beautifully! So did Ben's, which just goes to
show that there are many ways to approach this.

 

[Guest]

Please Rate the Posting since it works and you are happy.
Robert