Insert & Sequentially Number Column

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!

--
Thanks!

Dee

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

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
--
Thanks!

Dee


"Ingolf" wrote:

> 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
>
>

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


dee wrote:
> 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!
>
> --
> Thanks!
>
> Dee

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


"Mark Driscol" wrote:

> 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
>
>
> dee wrote:
> > 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!
> >
> > --
> > Thanks!
> >
> > Dee
>
>

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


dee wrote:
> 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
>
>
> "Mark Driscol" wrote:
>
> > 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
> >
> >
> > dee wrote:
> > > 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!
> > >
> > > --
> > > Thanks!
> > >
> > > Dee
> >
> >

Give this a go:

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

Thank you ben! It worked beautifully.
--
Thanks!

Dee


"ben77" wrote:

>
> 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
>
>
> --
> ben77
> ------------------------------------------------------------------------
> ben77's Profile: http://www.excelforum.com/member.php...o&userid=35602
> View this thread: http://www.excelforum.com/showthread...hreadid=556990
>
>

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

--
Thanks!

Dee


"Mark Driscol" wrote:

> 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
>
>
> dee wrote:
> > 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
> >
> >
> > "Mark Driscol" wrote:
> >
> > > 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
> > >
> > >
> > > dee wrote:
> > > > 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!
> > > >
> > > > --
> > > > Thanks!
> > > >
> > > > Dee
> > >
> > >
>
>

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