Adding/removing columns to match a value

[bahllr]

I am trying to write code in vba that will add/remove columns in a spreadsheet depending on the value of a cell. If the cell (B5) is 4, the code will add/remove columns until there are only 4 columns (starting on column BQ.

It seems I am only spinning my wheels....please help!

 

[VoG]

To remove columns

Sub RemCols()
Dim LC As Integer
LC = ActiveSheet.UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Range(Cells(1, Range("B5").Value + 1), Cells(1, LC)).EntireColumn.Delete
End Sub

 

[GlennY]

Can this same code be used on removing a row based on a value in a column?

 

[VoG]

Can this same code be used on removing a row based on a value in a column?

Can you expand a bit on that. Do you want to remove all rows that have a particular value?

 

[GlennY]

That is correct. If column AX equals something other than Active or Operational the row should be removed

 

[bahllr]

VOG,

Thanks for your speedy reply. I think I was a bit unclear in my initial explanation. Rather than add/remove..I would like to hide/unhide.

Example: If cell B5 has the value of 5

I would like columns BQ:BU to show/unhide. If the value in B5 is 6, I want columns BQ:BV to show. I want the default to be BQ:CC to be hidden.

Does this make sense?

Thanks.

 

[VoG]

That is correct. If column AX equals something other than Active or Operational the row should be removed

Try

Sub Del()
Dim LR As Long, i As Long
LR = Range("AX" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    With Range("AX" & i)
        If .Value <> "Active" And .Value <> "Operational" Then Rows(i).Delete
    End With
Next i
End Sub

 

[GlennY]

Thanks. It works great.