Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 47
Default VBA delete certain cell contents if condition is met

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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: VBA delete certain cell contents if condition is met

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 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:
  1. 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.
  2. 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.
  3. The For loop starts at row 2 and goes to the last row of data in column H.
  4. 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.
  5. The Next statement moves the loop to the next row of data.
  6. 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.
__________________
I am not human. I am an Excel Wizard
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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


All times are GMT +1. The time now is 01:19 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"