Help needed in VBA, defining a variable range from excel

[centauri]

Was wondering if someone could help me with defining a range from data in excel.

I have a lot of data in excel that varies both by rows and columns. I need to define this array in VBA, but want it to change if more columns or rows are added e.g array may be from C6:k100 but then change to c6:z250, but don't know where to start. If I know the data will always start in c6 how can I write some code to pick up the correct size of the array?

Any help appreciated

 

[Andrew Poulsom]

Maybe:

Range("C6", Range("C6").End(xlDown).End(xlToRight)).Select

 

[VoG]

Perhaps

Sub test()
Dim LR As Long, LC As Long, MyRange As Range
With ActiveSheet
    LR = .UsedRange.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    LC = .UsedRange.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    Set MyRange = .Range(.Cells(6, 3), .Cells(LR, LC))
End With
End Sub

 

[centauri]

I've used your suggestion but get an error "object variable or with variable not set".

here is the code I've used

Dim Arrcols As Range
Arrcols = Range("c6", Range("C6").End(xlDown).End(xlToRight)).Select

have I done something wrong (new to vba!!)

 

[jim may]

If the cells to the right and downward from C6 are always form a contiguous conected block of cells, perhaps then

Set Rng = Range("C6").CurrentRegion

 

[VoG]

Try this

Dim Arrcols As Range
Set Arrcols = Range("c6", Range("C6").End(xlDown).End(xlToRight))

 

[centauri]

cheers guys,

just one quick question, how do I find out what the array size is now? e.g. I tried "msgbox arrcols " and was hoping to get something along the lines of arrcols(C6,m100). Can I get something like this to make sure it is working?

 

[jim may]

Msgbox Arrcols.address(0, 0)

 

[centauri]

Fantastic! I've been trying to do this for the last 4 hours, Mr excel is the bee's knees! Thanks a lot for all your help. (will add Mr excel as a fav)