Adding/removing columns to match a value

bahllr

Board Regular
Joined
May 7, 2009
Messages
62
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

Legend
Joined
Jun 19, 2002
Messages
63,651
To remove columns

Code:
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

Board Regular
Joined
Jan 22, 2009
Messages
103
Can this same code be used on removing a row based on a value in a column?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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

Board Regular
Joined
Jan 22, 2009
Messages
103
That is correct. If column AX equals something other than Active or Operational the row should be removed
 

bahllr

Board Regular
Joined
May 7, 2009
Messages
62
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

Legend
Joined
Jun 19, 2002
Messages
63,651
That is correct. If column AX equals something other than Active or Operational the row should be removed
Try

Code:
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
 

Forum statistics

Threads
1,081,530
Messages
5,359,347
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top