Solved

Replace blanks with AutoFilter

Posted on 2013-12-22
11
192 Views
Last Modified: 2013-12-24
I need to remove the absolute values in this code and place the words "SERVICE PART" in the cell that have a blank in column C.  How do I do this on only the visible cells after the AutoFilter is applied, and what is it returns nothing?  Please advise and thanks. -R-

'Need to put SERVICE PART on all filtered Blank cells
    ActiveSheet.Range("$A$1:$D$440").AutoFilter Field:=3, Criteria1:="="
    Range("C124").Select
    ActiveCell.FormulaR1C1 = "SERVICE PART                                    "
    Range("C124").Select
    Selection.FillDown

Open in new window

0
Comment
Question by:RWayneH
  • 6
  • 4
11 Comments
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39735163
Range("A1").Activate
ActiveCell.Offset(1, 0).Activate

Do Until IsEmpty(ActiveCell)
  If IsEmpty(ActiveCell.Range("C1")) Then
    ActiveCell.Range("C1").Value = "SERVICE PART"
  End If
  ActiveCell.Offset(1, 0).Activate
Loop
Range("A1").Activate

Open in new window


The above code snippet should provide what you are looking for.

Tom
0
 

Author Comment

by:RWayneH
ID: 39735178
The A1 references confused me,,,  Is this assuming that the autofilter is already apply?
I need to know if I can apply the autofilter without the absolute values.. because it different filters that I apply return different ranges from day to day.  The filter is applied to field 3 or column C, that is the cell that will be blank and thus needing the SERVICE PART added to were it was blank.  This soluation did not work.
 
Activate A1, is that just saying to put the active on the autofilter row? but then it goes one cell down?

The C1, references?  I want this to start on C2, it looks like it starts on the header line.  How will this react if the auto filter returns nothing?  .

Can you read this back to me in english so I can understand it better and were it is not working?  Thanks. -R-
0
 
LVL 80

Expert Comment

by:byundt
ID: 39735410
You can use the Special Cells method to capture just the blank cells. AutoFilter is not required.

The macro below offers two choices for picking blank cells from column C. The first method uses a fixed range C2:C440 (assumes that C1 is a header label). The second method assumes that the number of rows might vary, but column A will always be populated if data exists in that row. I commented out the first approach by putting a single quote at the beginning of the line.
Sub ServiceParts()
Dim rg As Range, rgBlanks As Range
With ActiveSheet
    'Set rg = Range("C1:C440")   'If the range is fixed
    Set rg = .Range("A2")       'If the number of rows varies with amount of data in column A
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))    'All the data in column A
    Set rg = rg.Offset(0, 2)                                        'Corresponding cells in column C
End With
On Error Resume Next
Set rgBlanks = rg.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rgBlanks Is Nothing Then rgBlanks.Value = "SERVICE PART"
End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 39735959
Ok... this is assuming the autofilter is set prior to running this.  I am using a formula gathered when running the VBA recorder.

ActiveSheet.Range("$A$1:$D$958").AutoFilter Field:=3, Criteria1:="="

What do I place in the formula to tell it to use whatever the active area is? instead of the
("$A$1:$D$958")   ??

It will never be the same range, we will not need Ln 4
When setting the range the recorder uses absolute values..  I hate that..  Any ideas? -R-
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39735978
The coding solution that I provided does not need the auto-filter.  It assumes that the list starts at A1 -- this can easily be adjusted if you list starts at a different row and/or column.  It will continue down the list until it hits an empty cell.  While it is moving down the list, it looks at the C column to determine if the cell is empty and if it is empty then it will make that cell "Service Part".

The reason why the code may look funny is that it is all relative based on the active cell.  If the active cell is A5 then activecell.range("C1") is really pointing to C5.  It makes it easier to code without have to determine what row you are in as it is relative.

Tom
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:RWayneH
ID: 39736006
This code did not work... with filter on or off.  There are numerious cells that have data in it prior to finding the blank ones..  that is why I wanted to use the autofilter.  It does not have to be autofiltered but I was wondering if it did not work because the first bunch of cells in column C are not blank.  Any ideas? -R-
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39736008
Is there data in every cell in column A?  The process stops as soon as it hits an empty cell in that column.  Can you provide a dummy sheet that reflects you data?

Tom
0
 

Author Comment

by:RWayneH
ID: 39736009
0
 
LVL 6

Accepted Solution

by:
ButlerTechnology earned 500 total points
ID: 39736026
The code failed as there are no blank cells in the C column.  The have lots of spaces in each cell that is blank.  I have adjust the if statement to change any cell in the C column that starts with a space"
Range("A1").Activate
ActiveCell.Offset(1, 0).Activate

Do Until IsEmpty(ActiveCell)
  If Left(ActiveCell.Range("C1").Value, 1) = " " Then
    ActiveCell.Range("C1").Value = "SERVICE PART"
  End If
  ActiveCell.Offset(1, 0).Activate
Loop
Range("A1").Activate

Open in new window

0
 

Author Comment

by:RWayneH
ID: 39736339
Thanks I did not see the space in the blank cells, good catch.  -R-
0
 

Author Closing Comment

by:RWayneH
ID: 39738537
Change the cells without a filter worked, however the question stated that I need to do this from the result of a filter and looping thru a larger data set.  Sorry for the low grade.
Thanks for the help.  -R-
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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