Next available row
 

I have a workbook with 6 worksheets - all the worksheets are related to the
same part numbers but each contains different information. The number of rows
on each worsheet varies based on the data requirements. Each data table
begins at cell A1 on the respective sheet.

I import a file to update these data on the various sheets but sometimes I
encounter a new part number that needs to be added to one or more sheets.
Presently I go to each sheet to get the row count so I know where the next
empty row is.

Is there another way to do this without going to each sheet?

Thanks

Next available row
 

lastRow = Sheets("Sheet2").Range("A" &
Sheets("Sheet2").Rows.Count).End(xlUp)

Note: the Sheets("Sheet2") in front of the Rows.Count is only needed if
the ActiveSheet is not a "Worksheet", i.e. a stand alone Chart. (I
found this out the hard way:)

Charles Chickering

Sandy wrote:
I have a workbook with 6 worksheets - all the worksheets are related to the
same part numbers but each contains different information. The number of rows
on each worsheet varies based on the data requirements. Each data table
begins at cell A1 on the respective sheet.

I import a file to update these data on the various sheets but sometimes I
encounter a new part number that needs to be added to one or more sheets.
Presently I go to each sheet to get the row count so I know where the next
empty row is.

Is there another way to do this without going to each sheet?

Thanks

Next available row
 

You can have your code check it for you. Assume it can be determined from
entries in column 1 on each sheet.

Sub getLastrows()
for each sh in Activeworkbook.worksheets
set rng = sh.cells(rows.count,1).End(xlup)(2)
msgbox sh.name & ": next empty row is " & rng.row
Next
End sub

--
Regards,
Tom Ogilvy

"Sandy" wrote:

I have a workbook with 6 worksheets - all the worksheets are related to the
same part numbers but each contains different information. The number of rows
on each worsheet varies based on the data requirements. Each data table
begins at cell A1 on the respective sheet.

I import a file to update these data on the various sheets but sometimes I
encounter a new part number that needs to be added to one or more sheets.
Presently I go to each sheet to get the row count so I know where the next
empty row is.

Is there another way to do this without going to each sheet?

Thanks