Avatar of nflynn85
nflynn85
 asked on

How to delete a specific cell values in specific rows

I have a specific row, "total respondents", that repeats over and over throughout this sheet. I need to be able to search each individual cell in each row and delete all cell values that are 5 or less (in some cases, there is a "<5", others there is a 1 or 2).

I would do a simple ctrl + f and replace but there could be situations where it would affect the mean and median in other sections of the worksheet.

I attached a screen shot so you can see what i'm looking at. It literally repeats like this throughout the sheet for about 4000 rows.

Can someone assist?
testing.PNG
VBASpreadsheetsMicrosoft Excel

Avatar of undefined
Last Comment
Professor J

8/22/2022 - Mon
Professor J

you can use condictional formatting putting <=5 and put color then filter by color and delete the values

or you can use the following VBA code.

make sure u run this on a copy.

Sub t()
Dim c As Range

For Each c In ActiveSheet.UsedRange
If c <= 5 Then
c.ClearContents
End If
Next c
End Sub

Open in new window

AnthonyHamon

  1. On row 1 of the workbook, create a temporary header row and name the columns A, B, C, D, E etc.
  2. Mark all used cells in the workbook, go to the 'Data' tab and select 'Filter'
  3. Go to the top of the workbook
  4. Locate column that contains  the data labels (look likes column C from your attachment) and use the filter drop down on that column to show only the 'Total respondents' rows
  5. Use a simple find and replace which will only affect the visible cells
  6. Remove the filter
  7. Remove the temporary header
nflynn85

ASKER
I'd really prefer to do this in/with VBA so I can run it again next year.

The VBA Code provided ended up wiping out values that I did not want wiped out. I want it focused only on the row for total respondents. Is that possible? Being able to replace it with —
would be really cool

Take a look at the updated picture with the code that I ran from above (testing vs testing2)
testing.PNG
testing2.PNG
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Professor J

@nflynn85

if you want to replace it with —  then use below code

Sub test()
Dim c As Range

For Each c In ActiveSheet.UsedRange
If c <= 5 Then
c.Value = "—"
End If
Next c
End Sub

Open in new window

nflynn85

ASKER
Thanks for that update, but there was a prior issue that wasn't addressed. The values that are 5 or less need to be found on a case sensitive basis. I can't have 255 looking like 2-- or 374 looking like -7-

Does that make sense?
SOLUTION
Professor J

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
nflynn85

ASKER
Thanks for that. I ran the code on my excel sheet and it worked fine. However, it placed dashes in all of the empty columns/rows in between tables

While I can see and read and understand what your code is doing, I don't know how to modify it so that the above code doesn't place the dashes in empty cells (see attached photo)
dashes.PNG
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
nflynn85

ASKER
Thank you sir! Works like a charm. Was able to modify it to take out additional values as well. Thanks for the assist
Professor J

You're welcome I'm glad I was able to help