About Us

Sabosis

Hello

I have a spreadsheet that will have variable row counts each time I process it. I need to clear a few cells if the date in column H is greater than column F, or if column H is blank. Cells to be cleared would be in columns H, I, M, and N. I would need this to loop to the bottom of the sheet from row 2 to the bottom. Any help is appreciated! Thanks

ExcelBanter AI

Sure, I can help you with that. Here's a VBA code that should do the trick:

Formula:


			
Sub ClearCells()

Dim lastRow As Long
Dim i As Long

lastRow = Cells(Rows.Count, "H").End(xlUp).Row

For i = 2 To lastRow
    If Cells(i, "H").Value = "" Or Cells(i, "H").Value  Cells(i, "F").Value Then
        Cells(i, "H").ClearContents
        Cells(i, "I").ClearContents
        Cells(i, "M").ClearContents
        Cells(i, "N").ClearContents
    End If
Next i

End Sub

Here's how it works:

The first line declares two variables: lastRow and i. lastRow will be used to determine the last row of data in column H, and i will be used to loop through each row of data.
The second line uses the
Code:
```
Cells
```
function to find the last row of data in column H. It starts at the bottom of the sheet and goes up until it finds the last cell with data.
The For loop starts at row 2 and goes to the last row of data in column H.
The If statement checks if the date in column H is blank or greater than the date in column F. If either of these conditions are true, then the cells in columns H, I, M, and N for that row are cleared.
The Next statement moves the loop to the next row of data.
Once the loop is finished, all cells in columns H, I, M, and N that meet the specified conditions will be cleared.

I hope this helps! Let me know if you have any questions.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
changing cell contents based on a condition	Mike	New Users to Excel	6	May 1st 09 02:10 PM
Pull cell contents from one wksh to another if condition is met	Susan	Excel Worksheet Functions	3	April 22nd 09 03:41 PM
Formula: copy/concatenate cell contents matching condition	Neil Hutchinson	Excel Discussion (Misc queries)	4	September 2nd 08 09:51 AM
Delete row 1 based on condition in cell on row 2	McDuck	Excel Worksheet Functions	0	July 30th 08 07:53 PM
Is there a way to delete a cell value based on a condition?	Peanut	Excel Discussion (Misc queries)	2	October 2nd 06 09:55 PM

Menu

About Us