VBA macro- Range Function

  • Thread starter coolcp
  • Start date
Hi,

How do we select the complete range in the excel file. For example, I hv files with differnt rows and cols and i wanted to write a code to copy the data frm these datasheets to another..so cant 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

Homework Helper
1,667
369
The best way is to convert your range into a table and refer to that.

Failing that, defining a named range as
Code:
=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:
Code:
' 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
Code:
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.
 

Want to reply to this thread?

"VBA macro- Range Function" You must log in or register to reply here.

Physics Forums Values

We Value Quality
• Topics based on mainstream science
• Proper English grammar and spelling
We Value Civility
• Positive and compassionate attitudes
• Patience while debating
We Value Productivity
• Disciplined to remain on-topic
• Recognition of own weaknesses
• Solo and co-op problem solving
Top