Need help Finding row number of the last row with data.

[Fawks]

I'm trying to build a worksheet to store all of my DVD's on. This will
include columns with Title, Wide/Full, Director, Actor, Actress, Release
Date, ... (the columns may grow as needed/desired)

1. I'm trying to write a macro that will find the bottom row that has data
on it.
2. Then what I want to do is Sort the data by title or director or actress,
3. Copy all of the worksheet to a SortedByTitle, SortedByDirector etc.
worksheet,
4. Add an index column that goes from 1 to (bottom row),
5. Save SortedByX worksheet as html file.

Essentially I want to view the sorted html file on my PDA and have it show
the row number for each title. Eventually, I will need to break this up into
smaller html pages (i.e. rows 001-50.html, 051-100.html, 101-150.html and so
on), but that is way on down the line.

I CAN do numbers 2, 3 and 5. I need HELP finding out how to do numbers 1
and 4.

Thanks

 

[Nick Hodge]

Fawks

1) If your data is contiguous, as it should be in a list and the headers are
in row 1. this should give you the last row number

Sub findlastrow()
Dim lLastRow As Long
lLastRow = Application.WorksheetFunction.CountA(Range("A:A"))
End Sub

2) Added to number 1

Sub FillNewSeries()
Dim lLastRow As Long
lLastRow = Application.WorksheetFunction.CountA(Range("A:A"))
With Range("F1")
.Value = "1"
.AutoFill Destination:=Range("F1:F" & lLastRow), Type:=xlFillSeries
End With
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.excelusergroup.org
web: www.nickhodge.co.uk

 

[SixSigmaGuy]

I have to do this a lot, so I've written utility routines that do this for
me. The first function returns the last row that contains data in the
worksheet and column you pass as parameters. The second function does the
same thing except it returns the last column with data in the row you
specify. Hope these functions help.

Function iLastRowFilledInColumn(ws As Excel.Worksheet, iColumn As Long) As
Long
iLastRowFilledInColumn = ws.Cells(ws.Rows.Count, iColumn).End(xlUp).Row
End Function

Function iLastColumnFilledInRow(ws As Excel.Worksheet, iRow As Long) As Long
iLastColumnFilledInRow = ws.Cells(iRow,
ws.Columns.Count).End(xlToLeft).Column
End Function

 

[Fawks]

Nick and SixSigmaGuy

Thanks for your help. Nicks example was a little closer to what I was
looking for. It took me a little time to play with Nicks example and get it
right, but it works. Here is what I wound up with.

*********************

' ***** Finds last row then creates index from column A row two to column
A last row
' ***** then adds a new column to the left of A, fills it with lLastRow
index and formats the text in the new column
Dim lLastRow As Long
' Initialize lLastRow variable
lLastRow = Application.WorksheetFunction.CountA(Range("A:A"))
' Find the row number of the last row with data in column A
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
' Adds a new column to the left of column A
With Range("A2")
' begin a WITH loop starting at cell F2
.Value = "1"
' initial autofill value
.AutoFill Destination:=Range("A2:A" & lLastRow), Type:=xlFillSeries
' autofill column F starting at row 2, ending at column F row number
lLastRow ** My worksheet does have a TITLE row

' **What is xlFillSeries??? I have tried to look up that in Excel 2002
help files and Help does not help. lol**
End With
' ends WITH loop
Range("A" & lLastRow).Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
Columns("A:A").Select
' formats text in new column
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Columns("A:A").EntireColumn.AutoFit

*********************

Again THANKS for both of your input.

Fawks