Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Replace blanks with AutoFilter

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
RWayneH
Asked:
RWayneH
  • 6
  • 4
1 Solution
 
ButlerTechnologyCommented:
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
 
RWayneHAuthor Commented:
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
 
byundtCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
RWayneHAuthor Commented:
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
 
ButlerTechnologyCommented:
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
 
RWayneHAuthor Commented:
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
 
ButlerTechnologyCommented:
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
 
RWayneHAuthor Commented:
0
 
ButlerTechnologyCommented:
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
 
RWayneHAuthor Commented:
Thanks I did not see the space in the blank cells, good catch.  -R-
0
 
RWayneHAuthor Commented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now