Selecting Cells to set up Advanced Filter

  • Thread starter Thread starter Bruce Roberson
  • Start date Start date
B

Bruce Roberson

I am trying to select a range through a macro that starts
with an offset three cells above a range named start. Line
1 does this correct. But I then want to continue selecting
a cell that is 4 cells down without losing the first
location I selected. But line 2 makes me lose the first
cell I selected.

Then, after I've done that, I need to select the end down
and to the end of the right range as lines 3 and four
below do.

Once I've Highlighted all that, I want the macro to give
this area a range name called "UploadRange", but I don't
want it referring to an absolute range like it does here
in line 6 of this sub.

Once I've completed that then lines 7, 8, and 9 here
should be able to execute the advanced filter based on the
constant range called "criti". I think that lines 7, 8,
and 9 here are ok, but let me know if they dont' look
right.

Then, when I get through with the advanced filter, I had
it selecting a cell just to get rid of the highlight that
would be left from the advanced filter selections.


Sub HideZeros()
Range("Start").Offset(-3, 0).Select
Range("Start").Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="UploadRange",
RefersToR1C1:="=MJE!R10C1:R53C48"
Range("UploadRange").AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:= _
Range("Criti"), Unique:=False
Range("Q6").Select
End Sub
 
Sub HideZeros()
Range(Range("Start").Offset(-3, 0),Range("Start").Offset(1, 0)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="UploadRange",
RefersToR1C1:="=MJE!R10C1:R53C48"
Range("UploadRange").AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:= _
Range("Criti"), Unique:=False
Range("Q6").Select
End Sub

Might work.

Regards,
Tom Ogilvy
 
This didn't work but let's disect it line by line so I can
describe what I'm seeing. I ran it through step mode to
give the following observations.
-----Original Message-----
Sub HideZeros()
Range(Range("Start").Offset(-3, 0),Range("Start").Offset
Click to expand...
(1, 0)).Select
Bruce: At this point, cells a6 through a10 are highlighted
which is correct.
Range(Selection, Selection.End(xlDown)).Select
Click to expand...
Bruce: Nothing new happened here, but it should of
highlighted all the way down in column A; lets say to row
100 for the sake of discussion, and this could be
different each time. So absolute references do not work
here.
Range(Selection, Selection.End(xlToRight)).Select
Click to expand...
Bruce: After this, it did go all the way over to Column AV
which is correct, but again the command above did not
cause the highlighted cells to go below Row 6, so that is
the basis of the problem right now.
ActiveWorkbook.Names.Add Name:="UploadRange",
RefersToR1C1:="=MJE!R10C1:R53C48"
Click to expand...

Bruce: Step passed through here but no visible sign on the
screen of any changes. I don't see how the "Refersto"
reference would cause it to define the range according to
the cells selected. Isn't there a change needed in this
section?

Range("UploadRange").AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:= _
Range("Criti"), Unique:=False
Click to expand...

Bruce: Again no visible sign of anything on the screen,
but it can't do the advanced filter correctly anyway until
it selects all the rows it is supposed to select.

Range("Q6").Select
Click to expand...

Bruce: This just causes the macro to select a cell outside
the highlighted ones after the advanced filter has done
its thing.

Summary: It looks like it might work correctly if line 2
would just cause the thing to skip on down to the last row
like it should. The bad thing about Excel is it doesn't
always respond to cursor movements properly from what I've
seen so far. But since I do not want to be guilty of
trying to make something work exactly the way it did in
Quattro Pro, if I need a totally different approach to my
problem then I am open to that.

In a nutshell, all this is for is to identify any rows of
data that have a zero in both of two specified columns,
and hide those away from the records so that when I copy
and paste what is left in the advanced filter to a blank
worksheet for uploading, that only those records that
don't have a non zero value in one of the two specified
columns. Each row will always have a zero in one of the
two columns, but if it has a zero in both, then I don't
want it for this copy and paste operation if that makes
sense.
 
Stray comma - corrected code:

Sub HideZeros()
Range(Range("Start").Offset(-3, 0),Range("Start"). _
Offset(1,0)).CurrentRegion.Select
Selection.Name = "UploadRange"
Range("UploadRange").AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:= _
Range("Criti"), Unique:=False
Range("Q6").Select
End Sub

If you select
A6 to A10 and do Ctrl+Shift+8

and it selects your data, then the above should work. Realistically, if you
are putting in an advanced filter, you should be able to select a single
cell in the first row (or any cell in the data table) and do Ctrl+Shift+8

Range("Start").CurrentRegion.Select should probably be sufficient for all
the selecting you "need" to do (you really don't need to do any)

Sub AppyFilter()
Range("Start").CurrentRegion.AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:= _
Range("Criti"), Unique:=False
End Sub

should work.
 
Back
Top