[RESOLVED] Return number of rows, integer replacement

[rjpaulisick]

Hi all,

I'm looking for some code that returns the total number of rows in a spreadsheet (e.g. total number of rows - 6), and then uses that number to replace code in an existing macro.

For example, in a worksheet, 61 is the total number of rows. I'm searching for code that counts the number of rows and subtracts 6.

Here's the beginning of my code:

Rows("1:6").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:F").Select
Selection.Delete Shift:=xlToLeft
Range("A1:J55").Select
Selection.AutoFilter
ActiveWindow.SmallScroll Down:=-15
Range("C3").Select
Range("A1:J55").Sort Key1:=Range("C1"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

In this example, the total number of rows is 61, but because I'm deleting the first 6 rows at the beginning, all subsequent references to the total number of rows is 61 - 6, or 55, like J55.

Is there code that can be written that will automatically count the number of rows and subtract 6 from that returned value (before the macro is run) and replace it in the existing code (after the 6 row deletion)?

If it sounds confusing, I can further clarify upon request.

Thanks!

[Hack]

Moved to Office Development

[pgag45]

something like ...

dim rowCount as Integer
rowCount = ActiveSheet.UsedRange.Rows.Count -6

don't know if this is what you are looking for?

[rjpaulisick]

yes that helps very much so! i appreciate it!

one other question...

i want that returned integer to replace a number as it's written in a cell...like J55. representing it as JiAreaCount does not work obviously. how do i have that value returned as it would be properly written for a cell?

[rjpaulisick]

iAreaCount = rowCount, i just called it a different variable.

[pgag45]

Range("A1:J" & iAreaCount)

number of ways to accomplish this, but I use the above one normally...

[rjpaulisick]

AWESOME. appreciate the help!