Delete columns based on a cell reference date value
 

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

Delete columns based on a cell reference date value
 

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


On 18 Lis, 08:59, fishy wrote:
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

Delete columns based on a cell reference date value
 

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

On 18 Lis, 08:59, fishy wrote:
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

Delete columns based on a cell reference date value
 

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


"Jarek Kujawa" wrote:

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

On 18 Lis, 08:59, fishy wrote:
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

.

Delete columns based on a cell reference date value
 

thks Jacob

On 18 Lis, 10:17, Jacob Skaria
wrote:
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



"Jarek Kujawa" wrote:
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

On 18 Lis, 08:59, fishy wrote:
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

.- Ukryj cytowany tekst -

- Pokaż cytowany tekst -