Solved

Replace blanks with AutoFilter

Posted on 2013-12-22
11
206 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 81

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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
 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

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…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

809 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