Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Last Row and Column

  Asked By: Ayden    Date: Sep 30    Category: MS Office    Views: 1726
  

I have been asked by a colleague of mine to create a
spreadsheet which identifies the last row and column
when the spreadsheet is populated with data. The data
can vary month on month because its from a third
party. Therefore the last row and column will not
always be the same.

After the last column of data I would need to insert a
number in the column next to the last column of data.
The "number" would be the same as the row number. The
row numbering will end when the last row of data is
reached.

I hope the above makes sense.

I would appreciate a code or any suggestions about how
I should approach this.

Share: 

 

12 Answers Found

 
Answer #1    Answered By: Scott Anderson     Answered On: Sep 30

Here's one approach:
When in Excel, entering Cntl-End will take you to the last row/column.
In VBA, you use:

ActiveCell.SpecialCells(xlLastCell).Select
Then Selection.Row and Selection.Column are the row  and Column
Cells(1,Selection.Column + 1) = Selection.Column + 1
Cells(Selection.Row + 1,1) = Selection.Row + 1
will place the Row number  and column  Number in the next row or Column.

This approach  can be unpredictable, because if data  is removed, the
columns will not be identified as "blank" until the file is saved and
re-opened.

Are there any rows or columns that you KNOW will have data?
(column headings, perhaps a serial number?)
If so, you can use worksheetfunction counta to count the rows or
columns.

 
Answer #2    Answered By: Abaddon Cohen     Answered On: Sep 30

Thanks for your reply. I will try your suggestion and
let you know.

 
Answer #3    Answered By: Jacqueline Long     Answered On: Sep 30

Instead of a code  why dont u use function Row()? Choose the last column  and in
the column next to it write "=ROW(cell from previous column)"

 
Answer #4    Answered By: Joe Evans     Answered On: Sep 30

This doesn't always give me the last row.

Also it does not number  each row  in the "last column"
after the data. I need it to place a number going down
the page against each row which has data  in the last
column.

The data has column  headings for each of the 26+
columns and each row begins with a client name.

 
Answer #5    Answered By: Mable Stone     Answered On: Sep 30

I've kept this bit of information for my use -

Identifying the Real Last Cell

This is among the most valuable of all VBA techniques in Excel development.
There always seems to be a need to locate the last record in a table of
data. The most commonly used ways for doing this have significant pitfalls.

The Worksheet object's UsedRange does not always work because the used range
(or "dirty area") of a spreadsheet  may be larger than the area actually
populated with your records.

The Range object's CurrentRegion property is not the best either because
non-contiguous or incomplete records can cause an inaccurate reading of the
number of used rows.

The Range object's End method fails whenever you use the xlDown argument if
it encounters a blank cell in the column  being evaluated. However, if you
use this same technique instead with the xlUp argument, starting from the
bottom of the worksheet or just beneath the used range, it is almost
bulletproof.

Even so, we've found even a better way. The sample procedure below is a
variation on a technique I learned from fellow Excel MVP, Bob Umlas. It has
been put to the test and I believe it's the very most reliable way to
accomplish this.

Example:

Function LastCell(ws As Worksheet) As Range
Dim LastRow&, LastCol%

' Error-handling is here in case there is not any
data  in the worksheet

On Error Resume Next

With ws

' Find the last real row

LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

End With

' Finally, initialize a Range object variable for
' the last populated row.

Set LastCell = ws.Cells(LastRow&, LastCol%)

End Function


Using this Function:

The LastCell function shown here would not be used in a worksheet, but would
be called from another VBA procedure. Implementing it is as simple as the
following example:

Example:
Sub Demo()

MsgBox LastCell(Sheet1).Row

End Sub

 
Answer #6    Answered By: Guillermo Cox     Answered On: Sep 30

Being one of the perennial questions, shouldn't this be in the Files
area? Didn't see it.

 
Answer #7    Answered By: Anat Massri     Answered On: Sep 30

It was very useful but doesn't
quite answer my question.

I need to be able to automatically via a macro to
identify the last column  and row  and then in the last
column with data  insert another column which would be
populated with row numbers. This row numbering would
"end" once the last row of data is reached.

I can send a sample file if you like.

 
Answer #8    Answered By: Jawahir Burki     Answered On: Sep 30

The following allows you to run a macro which will present an input box.
When the input box is present you may select the last column  with your
mouse.

Sub NumberRows()
Dim rRange As Range
Dim sPrompt, sTitle As String
Dim lRangeCount As Long
Dim lColumn As Long
Dim sRange As String
Dim l As Long

'Makes Input Box setup easier to read
sPrompt = "Enter some text for your input box" 'This may not be
necessary but it looks nice
sTitle = "A title for input box" 'Again not necessary but it looks good
On Error Resume Next
Set rRange = Application.InputBox(Prompt:=sPrompt, Title:=sTitle, _
Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then 'In case user hits the cancel button.
Resume 0
Exit Sub
End If
Resume 0 'Always resume 0 whenever you have an on error resume next
statement

'The following was the only way I could find to properly select and
identify the starting active cell based on the input box selection.

sRange = rRange.Address
Range(sRange).Select
Set rRange = Application.ActiveCell 'Identifies starting row  and column
sRange = rRange.Address
Range(sRange).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.EntireColumn.Insert Shift:=xlToRight 'Inserts a column to the
right of your selected data.

lColumn = rRange.Column
lRangeCount = Cells(Rows.Count, lColumn).End(xlUp).Row 'As Dawn
mentioned this line of code  is only efficient when there are no blank
cells between the start and end  of the selected range.

'This will number  the rows for you.
For l = 1 To lRangeCount 'Start at 1 to skip header row, use 0 to start
at first row
ActiveCell.Offset(l, 0).Value = l + 1
Next

End Sub

 
Answer #9    Answered By: Pearl Garza     Answered On: Sep 30

All I am is good at keeping bits of code  organized and pulling
them out when appropriate!

 
Answer #10    Answered By: Finn Jones     Answered On: Sep 30

Sub NumberRows()
Dim rRange As Range
Dim sPrompt, sTitle As String
Dim lRangeCount As Long
Dim lColumn As Long
Dim sRange As String
Dim l As Long

'Makes Input Box setup easier to read
sPrompt = "Enter some text for your input box"
sTitle = "A title for input box"
On Error Resume Next
Set rRange = Application.InputBox(Prompt:=sPrompt, Title:=sTitle, _
Default:=ActiveCell.Address, Type:=8)
If Err.Number = 424 Then 'In case user hits the cancel button.
Resume 0
Exit Sub
End If
Resume 0

sRange = rRange.Address
Range(sRange).Select
Set rRange = Application.ActiveCell
sRange = rRange.Address
Range(sRange).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.EntireColumn.Insert Shift:=xlToRight
lColumn = rRange.Column
lRangeCount = Cells(Rows.Count, lColumn).End(xlUp).Row

For l = 1 To lRangeCount
ActiveCell.Offset(l, 0).Value = l + 1
Next

End Sub

 
Answer #11    Answered By: Ramon Davis     Answered On: Sep 30

It works except for the fact
that it does not number  the first row  as "1" but it
numbers the row after the last row ; eg. I have 672
rows with and it places a number in the next column
against each of the rows except number 1 and also
places a number against 673.

 
Answer #12    Answered By: Sarah Campbell     Answered On: Sep 30

Post the following code  into a VBA module. Then click any cell on the worksheet
where you want to add the row  numbers (to make sure it is the active sheet).
From the Tools menu, select Macro >> Macros >>AddRowNbr >> Run.

Sub AddRowNbr()
'Declare variables.
Dim LastRng As String, c As Range
'Find the last cell on the sheet.
LastRng$ = FindLastCell(ActiveSheet)
If LastRng$ = "ERROR" Then
MsgBox "Can't find last cell", , "AddRowNbr"
End If
'Set c to be the last cell.
Set c = Range(LastRng$)
'Go to row 1 in the next column  after c.
Cells(1, c.Column + 1).Activate
'Number all the rows of data.
ActiveCell.Value = 1
ActiveCell.Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, _
Date:=xlDay, Step:=1, Stop:=c.Row, Trend:=False
'Free the object variable.
Set c = Nothing
End Sub

Private Function FindLastCell(Wksht As Worksheet) As String
'Returns address of last cell used (highest row & col)
'on specified sheet.
Dim LastRow As Long
Dim LastCol As Integer
On Error GoTo FLCerr1
With Wksht
LastRow = 0
LastCol = 0
LastRow& = .Cells.Find("*", After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol% = .Cells.Find("*", After:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
Exit Function
FLCerr1:
'Empty worksheet, or unknown error.
FindLastCell$ = "ERROR"
End Function

 
Didn't find what you were looking for? Find more on Last Row and Column Or get search suggestion and latest updates.




Tagged: