How to Dynamically Select Complete Range in Excel Using VBA Macro?

[coolcp]

Hi,

How do we select the complete range in the excel file. For example, I hv files with different rows and cols and i wanted to write a code to copy the data frm these datasheets to another..so can't use the lines like Range(A1:AJ50).Select..I require somthing generic to change the range depending upon the data in the sheet..please let me know if you know of any such command.

Also, need a smilar thing for pivot table..need to change sourcedata range R1C1 in the following command dynamically to cover the complete excel sheet..ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _"Sheet1!R1C1:R12C15"

Thanks
Candra

 

[pasmith]

The best way is to convert your range into a table and refer to that.

Failing that, defining a named range as

=OFFSET(SheetName!$A$1,0,0,COUNTA(SheetName!$A:$A),COUNTA(SheetName!$1:$1))

wlll get you a rectangular range assuming that none of the relevant cells in column A or row 1 are blank, and in VBA you can get a reference using whichever of the following applies:

' Naturally you are using Option Explicit, so variables must be declared before use.
Dim myRange as Range

' For workbook-scope names
Set myRange = ThisWorkbook.Names("NameOfRange").RefersToRange

'For worksheet-scope names
Set myRange = ThisWorkbook.Worksheets("SheetName").Names("NameOfRange").RefersToRange

'If you need to refer to a named range in a different workbook, replace "ThisWorkbook" with a 
'suitable Workbook object.

If you are in the unfortunate situation where you have to pull data from multiple workbooks created by other people which don't follow a standard template, then you may be able to use

Set myRange = Workbooks("WorkbookName").Worksheets(1).Range("A1").CurrentRegion

assuming the data always starts at A1 on the first worksheet and none of the relevant cells in column A or row 1 are empty.