Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Cells to set up Advanced Filter
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Cells to set up Advanced Filter
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 "Bruce Roberson" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Cells to set up Advanced Filter
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 (1, 0)).Select Bruce: At this point, cells a6 through a10 are highlighted which is correct. Range(Selection, Selection.End(xlDown)).Select 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 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" 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 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 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. Might work. Regards, Tom Ogilvy "Bruce Roberson" wrote in message ... 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Cells to set up Advanced Filter
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. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... 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 "Bruce Roberson" wrote in message ... 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 (1, 0)).Select Bruce: At this point, cells a6 through a10 are highlighted which is correct. Range(Selection, Selection.End(xlDown)).Select 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 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" 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 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 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. Might work. Regards, Tom Ogilvy "Bruce Roberson" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter when using Date Range Cells | Excel Discussion (Misc queries) | |||
Advanced Filter used with merged cells? | Excel Worksheet Functions | |||
advanced filter looking for blank cells | Excel Discussion (Misc queries) | |||
Advanced Filter for Empty Cells | Excel Discussion (Misc queries) | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) |