Excel VBA Newbies - Is there a better syntax to code the if

[Peter]

Hi:

I am new to VBA, I have the following code, in the If statement, how
can I code it better instead of using lots of "OR". Is there a better
way or string comparison.

======================================================================
Dim lcCol As String
lnColCount = ActiveSheet.UsedRange.Columns.Count 'Column Count
For j = 1 To lnColCount
lcCol = GetExcelColumn(j)

If lcCol = "G" Or lcCol = "H" Or lcCol = "AB" Or lcCol =
"AF" Then
Columns(lcCol + ":" + lcCol).Select
Selection.EntireColumn.Hidden = True
End If
Next j

======================================================================
Thank you
Peter

 

[Dave Peterson]

Maybe:

select case lcCol
case is = "G","H","AB","AF"
'do your stuff
case is = "anymore here?"
'do other stuff
case else
'do even more different stuff
end select

But maybe even easier would be to use the number of the column:

Dim lcCol As Long
lnColCount = ActiveSheet.UsedRange.Columns.Count 'Column Count
For j = 1 To lnColCount
select case lcCol
case is 7, 8, 27, 32
columns(lccol).hidden = true
end select
next j

or maybe even:

range("G1,H1,AB1,AF1").entirecolumn.hidden = true

And be careful:

ActiveSheet.UsedRange.Columns.Count

could be 3 if your used range were AA1:AC9.

I like this:

dim LastRow as long
dim LastCol as long
with worksheets("sheet1") 'whatever
LastCol = .UsedRange.Columns(.UsedRange.Columns.Count).Column
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
end with

If row 1 and column A is used (anywhere), then it's slight overkill. But every
once in awhile....