Can I set a variable to be equal to the range of an entire column?

[robs3131]

Hi,

I am trying to set a variable named idrange to column D in a sheet - the
values in this column will vary (ie - values may go through row 54 but then
may be updated to have values through row 67, etc). I need to capture all
cells in column D that have values as each cell in column D that does have a
value will be evaluated in a subsequent equation in the code. I have not
been able to figure out how to set my variable to equal the range of column
D...below is that code I have - I get a "Runtime error '1004';" error with
this code - is it possible to do what I'm trying to do?

Sub purgepayment()

Dim payment
Dim opentranpay
Dim rownumpay As Integer
Dim idrange As Range
Dim payrange As Range
Dim y As Integer

Sheets("Member ID Report Master").Activate
Set idrange = Range(Columns("D"))

 

[Ben McBen]

Simply

foobar = Columns(4)


Should do what you want. However, you probably want to drop the "activate"
part, as it is unecesarry if you use:

Sheets("Member ID Report Master").columns(4)

 

[Jay]

Hi Robert -

Change
Set idrange = Range(Columns("D"))

To
Set idrange = Columns("D")

---
It's redundant (and not permitted) to use an explicitly defined range as the
sole argument of the Range property. You've correctly called for column D
with the Columns property, but the Columns property (in Columns("D"))
already returns the range you want. Therefore, your extra use of 'Range'
emulates the redundant statement:

Range(Range("D165536")), which is not permitted.

Range("D165536") or Columns("D") is all that is needed. You can also
simplify to Columns("D").

 

[ma1achai]

To add to that, if you are only looking for cells that have a value, you can
try using the SpecialCells property.

Try this:
Set idrange = Columns("D").SpecialCells(xlCellTypeConstants, xlNumbers)

Or if you are looking for cells with other characteristics there are other
constants to use with SpecialCells... take a look in the help on it.

-jputman

 

[robs3131]

Hi Ben,

This is actually a good point that you have brought up. From prior posts of
mine, it has sounded to me like "columns" and "rows" refer to the active
sheet, which in your example below is not necessarily "Member ID Report
Master". I infer this from a response to an ealier post I had - this
response in listed below. Can you please let me know if I am missing
something? Basically, I want to understand whether or not I SHOULD assume
that Excel will see the rows and columns in such statements as being rows and
columns within the sheet that is referenced in that line of code.

Below is a response I had to an earlier post of mine:

Sheets("Payment Sales Master").Range(Rows(2),
Rows(2).End(xlDown)).ClearContents

- If in the "Payment Sales Master" WS, OK, as Rows refers to the same sheet
as Range.
- If in a module AND "Payment Sales Master" is the ActiveSheet, OK, as Rows
refers to the same sheet as Range.
- If in a module AND "Payment Sales Master" is NOT the ActiveSheet, ERROR,
as Rows refers to the ActiveSheet, whilst as .Range refers to
Sheets("Payment Sales Master").