Ranges...

[magelan]

How exactly can I use a range to build a range?

I have a dynamically generated range called "zipLocation" that is different for every file I run it against. ZipLocation is a single-cell range that is the header of the entire column that I want to check.

Basically, i want to build a range, like..

in pseudocode..


NewRange = (ziplocation.column)(ziplocation.row.offset(1,0):(ziplocation.column)(last used row of the sheet)


so if ziplocation was C6 and the last row was 200 i want the range to be C7:C200

but I've always run into trouble trying to get Range to work properly. How can I build this?

[CodeNinja]

magelan,
Something like this might show you how to accomplish what you want...

Sub range()
Dim rng As range

Set rng = Sheet1.range("A2:A" & Sheet1.range("A65536").End(xlUp).Row)
MsgBox (rng.Address)
Set rng = Sheet1.range(rng.Cells(1).Offset(1, 0).Address & ":A" & Sheet1.range("A65536").End(xlUp).Row)
MsgBox (rng.Address)



End Sub

[Bob Phillips]

How about simply

Set rng = Range(Range("zipLocation").Offset(1, 0), Range("zipLocation").End(xlDown))

[magelan]

How about simply

Set rng = Range(Range("zipLocation").Offset(1, 0), Range("zipLocation").End(xlDown))



Set checkRange = Range(zipLocation.Offset(1, 0), zipLocation.End(xlDown))


Your code kept giving me errors, but changing it to this worked. Thanks a ton!

[Bob Phillips]

Sorry, for some reason I thought zipLocation was an Excel name rather than a range variable.

[Teeroy]

One thing to keep in mind is that if there are there any blank cells in the column it may be necessary to grab the bottom cell of the column instead to define checkRange, e.g.



Set checkRange = Range(zipLocation.Offset(1, 0), cells(rows.count,zipLocation.column).End(xlUP))

[magelan]

One thing to keep in mind is that if there are there any blank cells in the column it may be necessary to grab the bottom cell of the column instead to define checkRange, e.g.



Set checkRange = Range(zipLocation.Offset(1, 0), cells(rows.count,zipLocation.column).End(xlUP))


So my code was erroring and only picking up 18000 lines instead of 54000 lines, now I figured out why because I remembered this post. Apparently there is a blank cell at 18000....

Good call teeroy!

[magelan]

Okay..more advanced question this time.

I have one range that I want the row of, and another range that I want the column of.



sub countryCheck(theRow as range, theColumn as range)
dim workingRange as range

'psuedocode .. set workingRange = theRow.row,theColumn.column

end sub

how do i set workingRange as theRow.row,theColumn.column?

From there, how do I go about running a check on this cell? (I want to do "if not workingRange.cells.value = US then delete the row)

edit - note - thecell.row is 2 and thecolumn.column is 4.. so I know its R1C1 notation, I just dont understand why ranges are so overly complicated to assign in VBA.

Second Edit -- NEVERMIND - figured out a method using set WorkingRange=Cells(theRow.row,theColumn.column)

[Teeroy]

Two ways I could think of. This is how I would do it.
set workingRange = Cells(theRow.row,theColumn.column)
You might need a worksheet qualifier (ie a full reference) in front of Cells, depending on what you are doing.

This is for information really. It has its uses, but not really here.
set workingRange = Intersect(theRow.row, theColumn.column)

[magelan]

Two ways I could think of. This is how I would do it.
set workingRange = Cells(theRow.row,theColumn.column) You might need a worksheet qualifier (ie a full reference) in front of Cells, depending on what you are doing.

This is for information really. It has its uses, but not really here.
set workingRange = Intersect(theRow.row, theColumn.column)

I had actually tried the intersect method and was completely unable to produce a working range. Stepping through my code, It would turn from "nothing" to "object with block range blah blah not set" the second it hit the Intersect. Since I'm doing all my work on one sheet at a time and iterating through the sheets, the Cells works really well.

[Teeroy]

D'oh! I forgot to remove the .row and .column for the intersect method during cut and paste. With those included you are just passing numbers; you need to remove them so as to pass the range object directly.