Solved

How to delete a specific cell values in specific rows

Posted on 2016-09-13
10
50 Views
Last Modified: 2016-10-04
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
0
Comment
Question by:nflynn85
  • 5
  • 4
10 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41796379
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

0
 
LVL 4

Expert Comment

by:AnthonyHamon
ID: 41796395
  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
0
 

Author Comment

by:nflynn85
ID: 41796610
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
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41818631
@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

0
 

Author Comment

by:nflynn85
ID: 41826544
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?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 500 total points
ID: 41826636
@nflynn85

the code above does not replace 255 to  2--

please see attached example.

if you run the code, it will only replace those highlighted cells and not any other value.
Book1.xlsm
0
 

Author Comment

by:nflynn85
ID: 41827062
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
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 41827111
Then try this

Sub test()
Dim c As Range

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

Author Comment

by:nflynn85
ID: 41828003
Thank you sir! Works like a charm. Was able to modify it to take out additional values as well. Thanks for the assist
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41828112
You're welcome I'm glad I was able to help
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now