using variables in visual basic range references
Posted by John on October 16, 2001 6:20 AM
I'm writing a macro in excel that I will be applying to several workbooks with varying numbers of rows. I would like to insert a variable (that describes the number of rows) into the range of cells that I select:
i.e., Range(RC:R@C).Select where @ is the number of rows that I want to select in each worksheet.
But so far, I've not been able to make this work.
One additional note to help keep this simple, the number of rows is listed in each workbook in the same cell (B1).
Thanks for your help.
J.
Posted by Jonathan on October 16, 2001 6:56 AM
ActiveSheet.UsedRange.Rows.Count
will give you the row number of the last row that has data in it (the last non-blank row) in a sheet.
Posted by Buzansky on October 16, 2001 4:00 PM
A small point .....
will give you the row number of the last row that has data in it (the last non-blank row) in a sheet.
"ActiveSheet.UsedRange.Rows.Count" will only give the last row if row 1 of the worksheet contains something.
Better to use :-
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
Posted by Buzansky on October 16, 2001 4:12 PM
Range("A1:A" & Range("B1").Value).Select
Posted by Jonathan on October 16, 2001 6:32 PM
Re: A small point .....
Thanx for the tip.
jg
