Delete columns based on a cell reference date value

  • Thread starter Thread starter fishy
  • Start date Start date
F

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
 
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
 
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
 
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
 
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 -
Click to expand...
 
Back
Top