VBA- how to select a range of columns?

[jkeyes]

In all of the instruction material I have read, this question is never answered. I know how to select a single column, but not multiple ones.

I need to delete a range of columns based on a variable column number established at the beginning of the sub. However, the various forms of selection I've tried return an error in the code.

Any and all help is greatly appreciated! I hope I've given enough info, please request more if necessary... thanks in advance!

jkeyes

 

[DaK888]

Private Sub CommandButton1_Click()
Columns("F:G").Select
Selection.Delete
End Sub

here is some code I tested with a button on a sheet. You could just substitute the column letter with you variable. I believe you would leave out the " marks if you use a variable.

Not sure if its what you are looking for.

DaK

 

[jkeyes]

Thanks, I've tried that...

With "NCN" as my variable, this is where my code falls apart...

ActiveSheet.Columns(NCN, NCN + 10).Select (The range symbol ":" is not allowed in this set up)

Thanks again for any and all help!

 

[Russell Hauf]

Here's one way to do it:

Range(Columns(NCN),Columns(NCN + 10)).Select


HTH,

Russell

EDIT: OOPS! Was thinking of something else when I gave this line earlier:

Columns(NCN & ":" & NCN + 10).Select

 

[jkeyes]

Thanks Russell.

Am I missing something though? I tried that before too, and it doesn't work. Logically, it should (I think), but for some reason it's not... aaarrrggghh!

 

[Russell Hauf]

Columns will only take a number or the column letters in a string (such as Columns("E:H") ).

The other way works just peachy though.

 

[jkeyes]

Russell-

If "the other way" you're talking about is:

Range(Columns(NCN),Columns(NCN + 10)).Select

I've tried that and it doesn't work (btw, I know my variable is valid b/c it works in other apps).

If the above is not "the other way" you were talking about, could you please specify what is? Thanks again for your help... this problem has been haunting me for a couple weeks now...

 

[Yogi Anand]

Hi jkeys:

One of the ways to accomplish that would be to use the following code ...

cells(1,NCN).resize(rows.Count,10).select

Does it help?

 

[Russell Hauf]

Russell-

If "the other way" you're talking about is:

Range(Columns(NCN),Columns(NCN + 10)).Select

I've tried that and it doesn't work (btw, I know my variable is valid b/c it works in other apps).

If the above is not "the other way" you were talking about, could you please specify what is? Thanks again for your help... this problem has been haunting me for a couple weeks now...

It works for me. What is the value of NCN when the code runs? If it is 0 or less, or if it is 247 or greater, then it won't work. Otherwise it will. Here is the code that I used to test it.

Sub Test()
    Dim NCN As Integer
    
    NCN = 5
    Range(Columns(NCN), Columns(NCN + 10)).Select
End Sub

 

[JASchmidt]

Hey Russ...I'm new here because of your little dittie above. I was close but the formatting can drive you nuts and you got me straightened out. Thanks a bunch. Crazy...11 years after you posted this. I love me some interwebs!