Quick Q: Reference multiple columns by number, not letter? 1

[VBAjedi]

Brainfreeze - I've forgotten how to reference multiple columns by number. I need to be able to set the width of, say, columns 2, 3, and 4 on a worksheet object using something like:

MyWorksheetObject.Columns(2:4).Width = "14.75"

I know I could do it one at a time with:

MyWorksheetObject.Columns(2).Width = "14.75"

What's the correct syntax?



VBAjedi

 

[DrJavaJoe]

Sheet1.Columns("B&quot.ColumnWidth = 14.75

 

[Zathras]

I don't think there's an easy way, but here is a kludge that you might be able to use:
[blue]

Sub test()
  SetColWidths FirstCol:=2, LastCol:=4, Width:=14.75
End Sub

Sub SetColWidths(FirstCol As Integer, LastCol As Integer, Width As Single)
  Range(Cells(1, FirstCol), Cells(1, LastCol)).EntireColumn.ColumnWidth = Width
End Sub

[/color]

 

[VBAjedi]

DrJavaJoe,

With all due respect, read the title of my thread and the first line of my post. Understanding my question will make answering it much easier! [LOL]

Zathras,

Using the .EntireColumn property of a range is a good idea. Bulky syntax, but it beats out my little three-line "For. . .Next" loop. Have a star.


VBAjedi

 

[RobBroekhuis]

Ah, but why not do it this way:

activecell.range(cells(1,1),cells(1,3)).EntireColumn.Interior.Color=vbred



Rob

 

[VBAjedi]

Sorry, Rob, but I'm not seeing how your suggestion improves on what Zathras suggested (other than not being wrapped in a test sub, which isn't really an improvement). Unless there's some hidden genius to referencing activecell. . . care to enlighten me on any lurking genius?

<grin>

VBAjedi

 

[RobBroekhuis]

Umm, sorry - the wrapping-in-sub tripped me up. You're right, they're the same. Silly me.


Rob

 

[VBAjedi]

No problem, that just means your inner genius is lurking a bit deeper than usual. . .

[LOL]

Thanks for the post, anyway!

VBAjedi