Reference a Column in Code

  • Thread starter Thread starter Wally Steadman
  • Start date Start date
W

Wally Steadman

below is a put together of a sub that I am trying to
accomplish where I will look at the top cell in a column
and if it meets the criteria, then I want it to copy that
column onto another worksheet to the first open column but
not sure how to reference columns in the code. When I
leave it as below with Columns ("A:A") it works fine, but
I need ("A:A") to equal colnum or something like that, but
when I try
Columns ("colnum:colnum") it does not work and I have
tried it without the quotes as well. Any assistance would
be appreciated. What the real question is, is how to
reference a column in code, I can get it to work for Row,
column but not sure how to get it to work for the entire
column. Code below is a slopy example. Thanks in advance

Wally Steadman

Sub one()
dim colct as VARIANT
dim colnum as (INTEGER or VARIANT)
colct = 1

Columns("G:G").Select
Selection.copy
Sheets("Sheet2").Select
Columns("A:A").Select
ActiveSheet.Paste
End Sub
 
Wally,

just use a single integer as the columne reference. For
example, this routine looks at the first ten columns in
sheet 1. If the value in row 1 is greater than 3, it
copies the whole column into sheet 2, starting at A and
moving on one each time a column is copied.

Dim X as Integer
dim y as integer

Y = 1
For X = 1 to 10

If Cells(1,x).value > 3 then
Columns(X).copy destintion:=sheets("Sheet2").cells(1,y)
y = y + 1
End IF

Next X


Cheers, Pete
 
Back
Top