How to make a function only apply to a variable number of rows containing data

[fatduck]

I'm writing a macro to apply some simple functions (copy, paste, sort, etc) to a sheet of data. My problem is that the number of rows in this sheet will vary for each occassion I want to apply the macro. Can someone please tell me how to instruct a function to first calculate the number of rows of data present, and then to use that value (e.g. 20,000 rows) to set the range upon which the function willl operate (e.g. A1:G20000 in this case)?
Thanks very much!

 

[Jon von der Heyden]

Perhaps something like:

Dim LastRow as Long, MyRng as Range

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Set MyRng = Range("A1:G" & LastRow)

....Rest of Macro here

There are many ways of doing this. Search for alternative methods by doing a search on the board for "LastRow" for instance.

 

[DonkeyOte]

r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count

 

[fatduck]

Thanks very much!