Deleting rows keeps rows with shaded cells

[anaconda777]

Hi all,

I used a code from this message board to delete rows where column C is blank, however it is keeping the rows where the cell is shaded with a color.

Clearing all colors prior to running the macro also does not seem to work.

Here is what I used. Any tips appreciated. PS - I am brand new at this.

 

[My Aswer Is This]

So, you want a script to see if the cell in column C is empty and if so delete this row is that correct?
And by empty, I mean there is no value in column C
And column A will always have data is that correct? So, the last row in this worksheet with data will Have data in column A is that correct? If not what column will always have some data to last row with data?

 

[anaconda777]

So, you want a script to see if the cell in column C is empty and if so delete this row is that correct?
And by empty, I mean there is no value in column C
And column A will always have data is that correct? So, the last row in this worksheet with data will Have data in column A is that correct? If not what column will always have some data to last row with data?

Thanks for the reply.

Yes. And yes, no value in column C.
Column A will not always contain data. In fact, none of the columns will always contain data on every row, some rows are totally blank and some are partially blank.

Lets say there are 10 columns and 50 rows, about 15 of those rows will have data in column C. The final result should consist of column C‘s 15 rows and the associated data in columns F and I. All of the other rows can be deleted. However, the cells in column C with no data but have color shading stick around. The columns I know how to delete with Record macro.

 

[My Aswer Is This]

Try this:

Sub Delete_Rows_If_C_Is_Blank()
'Modified 6/20/2022  10:50:49 PM  EDT
Application.ScreenUpdating = False
Dim ans As Long
ans = ActiveSheet.UsedRange.Rows.Count
Dim i As Long

For i = ans To 1 Step -1
    If Cells(i, 3).Value = "" Then Rows(i).Delete
Next
Application.ScreenUpdating = True
End Sub

 

[anaconda777]

Hi, thank you for the code. I am having difficulty incorporating that within my current code i.e. either some text turns red and/or there is an error about the sub.

The full code is attached. I tried to remove the circled part which was leaving the shaded cells and put in your code but I believe there may be an issue with two End subs back to back? I deleted one but still an issue.

 

[anaconda777]

Hang on I am going to try something else and reply again.

 

[My Aswer Is This]

My code should do the job you asked for.

Why do you think you need to use your code which does not work along with my code?

And if you have code, you should post the code here for me to see and not post an image of the code

 

[anaconda777]

On its own, what you provided has worked. Thank you so much. This puts me way ahead of where I was as that was the most onerous part.

When trying to incorporate it into my previous recorded code it was an issue. It could be because the columns changed earlier in the code versus what the code used later.

 

[anaconda777]

I will try to put it all together again beginning with your code and then the mostly inconsequential formatting and learn how to post the code and not an image. Thanks.

 

[My Aswer Is This]

On its own, what you provided has worked. Thank you so much. This puts me way ahead of where I was as that was the most onerous part.

When trying to incorporate it into my previous recorded code it was an issue. It could be because the columns changed earlier in the code versus what the code used later.

Using the Recorder is OK to try and learn Vba or for simple scripts but is not a good way to write a script. You should explain in detail what you're trying to do with specific details.

And anytime you have code it's best to post the code here in the forum and not an image of the code