Delete columns based on a cell reference date value

[fishy]

I have a sheet titled 'data' that has source information and row 1 has
columns H to BG with dates.

On sheet 'Control' is a cell that has a free format cell for dates to be
entered. I have named this cell 'WCDATA' for ease of another piece of code.

I am tring to create a macro that will locate the date specified in WCDATA
on the data sheet and delete all columns from H until that which matches
WCDATA.

Fortunately I have been running on a test file and I have tried several
options but after considerable hair pulling am seeking assistance as am
getting close to deadline now.

Any assistance appreciated.

R

 

[Jarek Kujawa]

Sub cus()
Dim cell As Range
For i = Range("H1").Column To Worksheets("data").Range
("H1:BG1").Cells.Count
If Cells(1, i).Value <> Range("WCDATA").Value Then
Cells(1, i).Columns.EntireColumn.Delete
i = i - 1
Else
Exit For
End If
Next i

End Sub

 

[Jarek Kujawa]

corrected

Sub cus()

For i = Range("H1").Column To Worksheets("data").Range
("H1:BG1").Cells.Count
Cells(1, i).Activate
If Cells(1, i).Value <> Range("WCDATA").Value Then
Cells(1, i).Columns.EntireColumn.Delete
i = i - 1
Else
Exit For
End If
Next i

End Sub

 

[Jacob Skaria]

You can avoid that looping

Sub Macro8()
Dim varfound As Range
Set varfound = Worksheets("Data").Rows(1).Find(Range("wcdata"))
If Not varfound Is Nothing Then
Worksheets("Data").Range("H1", Cells(1, varfound.Column)).EntireColumn.Delete
End If
End Sub

If this post helps click Yes

 

[Jarek Kujawa]

thks Jacob

You can avoid that looping

Sub Macro8()
Dim varfound As Range
Set varfound = Worksheets("Data").Rows(1).Find(Range("wcdata"))
If Not varfound Is Nothing Then
Worksheets("Data").Range("H1", Cells(1, varfound.Column)).EntireColumn.Delete
End If
End Sub

If this post helps click Yes
---------------
Jacob Skaria










- Pokaż cytowany tekst -