Update column and row references in a macro when new column or rows inserted

[NotBillGates]

Hi
If I have a worksheet with three columns A, B and C. I can write a macro

Columns ("B":"C").Select
Selection.EntireColumn.Hidden = True

If I insert new column B the data in B and C moves to C&D if you know what I mean
How do I get my macro to hide the new C&D?
I have tried defining the column reference in a cell using
SUBSTITUTE(ADDRESS(1,COLUMN(B2),4),"1","")
and then using it in a string but it doesn't work.

Would the same solution work for rows?

Thanks in advance

 

[wideboydixon]

Is there something at the top of those columns that could be used to identify them?

WBD

 

[NotBillGates]

Sorry, I probably over simplified the question. Row 1 for each column is unique e.g
B1 Additional Activity PLANNED
C1 Additional Activity UNPLANNED

 

[wideboydixon]

Public Sub HideTwoColumns()

Dim colNumber As Double

On Error Resume Next
Err.Clear
colNumber = WorksheetFunction.Match("Additional Activity PLANNED", ActiveSheet.Range("$1:$1"), 0)
If Err.Number <> 1004 Then
    ActiveSheet.Columns(colNumber).Resize(ColumnSize:=2).Hidden = True
End If

End Sub

WBD

 

[NotBillGates]

Brilliant, Thanks.
I tried changing the code to find a row and hide it but I guess I am doing something wrong. Text HideMe is in cell a12

Public Sub HideTwoRows()
Dim rowNumber As Double
On Error Resume Next
Err.Clear
rowNumber = WorksheetFunction.Match("HideMe", ActiveSheet.Range("1$:1$"), 0)
If Err.Number <> 1004 Then
    ActiveSheet.Rows(rowNumber).Resize(RowSize:=2).Hidden = True
End If
End Sub

 

[wideboydixon]

Try:

rowNumber = WorksheetFunction.Match("HideMe", ActiveSheet.Range("$A:$A"), 0)

WBD

 

[NotBillGates]

Thanks WBD, it worked a treat.

 

[NotBillGates]

Hi
Is there any way to use this in a macro for a filter, so that filter range expands as new rows are added.
e.g Current range is $A$7:$C$156 with "LastRow" in A156
insert new row 150 so "LastRow" now in A157
I would like the macro to always filter down to the row that contains "LastRow"

Public Sub HideTwoRows()
Dim rowNumber As Double
On Error Resume Next
Err.Clear
rowNumber = WorksheetFunction.Match("LastRow", ActiveSheet.Range("$A:$A"), 0)
If Err.Number <> 1004 Then
' Replace ActiveSheet.Rows(rowNumber).Resize(RowSize:=2).Hidden = True
' With
ActiveSheet.Range("$A$7:$C$rowNumber").AutoFilter Field:=1, Criteria1:="Fred"
End If
End Sub