select last visible cell in autofilter

[keithkemble]

HI Folks,

I have having difficulty in determining how to find the last visible cell in a filtered selection and would appreciate any pointers

(Objective - select visible range - to select first visible cell column A to last visible cell column k then clear contents) having already filtered for 3 criteria (first data row 13)

The selection must only be visible cells. and would use the the clearcontents command for the range as there is data to the right of column K.

I currently have a macro that asks for 3 lots of criteria for the autofilter
Then filters leaving the visible cells

Then finds the first visible cell - which is stored to a variable

Unable then to determine / find / locate (via VBA) the last visible cell/ row (endup etc does not find the visible only the last physical cell/row)

thanks

 

[pedie]

Not best but easiest way is...

try something like this

[/FONT]
[FONT=Courier New]ActiveSheet.Range("$A$1:$K$89").AutoFilter Field:=1, Criteria1:="=EMP 1", _
        Operator:=xlOr, Criteria2:="=EMP 2"
    ActiveSheet.Range("$A$1:$K$89").AutoFilter Field:=3, Criteria1:="2"
    MsgBox "Last visible cell now is " & Range("A1").End(xlDown).Address

 

[keithkemble]

Thanks Pedie,

Naturally "I could not see the wood for the trees "

Don't know if its my hangover or what but the "address" is obvious really.

Appreciated
K

 

[Peter_SSs]

Keith

How does your code determine the range to AutoFilter in the first place?
Can column A be relied upon to determine the last row of actual data (before anything is filtered? That is, could we use something like this to determine the LastRow..

LastRow = Range("A" & Rows.Count).End(xlup).Row

.. and then filter from A1:K&LastRow?

 

[keithkemble]

HI Peter,

Cell a12 is the anchor of the autofilter (across all of row 12) with data commencing always at A13.

yes & yes.

Equally column A will always have mandatory data so last row can always be relied upon.

Cheers

Ps I have sorted the routine out now with the aid of Pedie pointing out my error; however if there was some simplistic statement to shorten my routine, that finds & selects first (top left - column A) and last (bottom right - Column K) of ONLY visible data it would be greatly appreciated

 

[pedie]

Are you wanting something like this?

Or just select two cells that is top left and K column last cell respedtivecell...?

[/FONT]
[FONT=Courier New]Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row[/FONT]
[FONT=Courier New]    ActiveSheet.Range("A1:K" & lr).AutoFilter Field:=4, Criteria1:=Array("2", _
        "3", "4", "5"), Operator:=xlFilterValues
    
    
    ActiveSheet.Range("A1:K" & lr).SpecialCells(xlCellTypeVisible).Select
    
    MsgBox "The following address is selected " & Selection.Address

 

[Peter_SSs]

... however if there was some simplistic statement to shorten my routine, that finds & selects first (top left - column A) and last (bottom right - Column K) of ONLY visible data it would be greatly appreciated

Keith, a few comments.

1. There is no need to actually select the cells to clear them. It is rare in vba to need to select things to work with them. Selecting also slows your code considerably.

2. I'm not sure if you have it in place already, but turning off ScreenUpdating while your code runs can also speed your code considerably and stop screen flicker. This flicker can be considerable particularly when filters are being applied & removed.

3. I'm not sure if it will be possible with your data/filters but just in case there are no visible rows (apart from the header row) after your filters are applied, it is a good idea to use the "On Error" structure I have shown below. Otherwise the code will error if there are no visible cells.

So, here's my suggested structure for you. You'll need to fix up the Filter part to suit what you are doing, but I gather you have that part of the code under control.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Clear_Filtered_Data()<br>    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    LastRow = Range("A" & Rows.Count).End(xlUp).Row<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("A12:K" & LastRow)<br><br><SPAN style="color:#007F00">'        Put your AutoFilter codes here. Along the lines of ..</SPAN><br><SPAN style="color:#007F00">'        .AutoFilter Field:= .. Criteria1:= ..</SPAN><br><SPAN style="color:#007F00">'        etc</SPAN><br><br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        .Offset(1).Resize(.Rows.Count - 1) _<br>            .SpecialCells(xlCellTypeVisible).ClearContents<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        <br><SPAN style="color:#007F00">'        Remove the AutoFilter</SPAN><br><SPAN style="color:#007F00">'        .AutoFilter</SPAN><br>        <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[keithkemble]

Cheers Peter,

Looks interesting.

1 I always select as I like to see the action when using F8 that way I can test the routines; Syntax is a wonderfull thing if you are fluent in it. Once I understand how the routine works I then look to simplify the code.

2 I always turn off screenupdating - speed up the routines massively.

3 Like wise I always put an error catch code in as I have been caught out a number of times over the years.

I will give your code a try later today. I have the neccessary code to fill in and so it looks very promising.

Thank you for taking the time out; appreciated.

Regards

 

[Rick Rothstein]

Here are two functions that I have posted in the past over in the Microsoft Answers forum; perhaps you can make use of them for your current request...

Function GetFilteredRangeTopRow() As Long
  Dim HeaderRow As Long, LastFilterRow As Long
  On Error GoTo NoFilterOnSheet
  With ActiveSheet
    HeaderRow = .AutoFilter.Range(1).Row
    LastFilterRow = .Range(Split(.AutoFilter.Range.Address, ":")(1)).Row
    GetFilteredRangeTopRow = .Range(.Rows(HeaderRow + 1), .Rows(Rows.Count)). _
                             SpecialCells(xlCellTypeVisible)(1).Row
    If GetFilteredRangeTopRow = LastFilterRow + 1 Then GetFilteredRangeTopRow = 0
  End With
NoFilterOnSheet:
End Function
 
Function GetFilteredRangeBottomRow() As Long
  Dim HeaderRow As Long, LastFilterRow As Long
  On Error GoTo NoFilterOnSheet
  With ActiveSheet
    HeaderRow = .AutoFilter.Range(1).Row
    LastFilterRow = .Range(Split(.AutoFilter.Range.Offset(1).Address, ":")(1)).Row
    GetFilteredRangeBottomRow = .Range(.Rows(HeaderRow + 1), .Rows(LastFilterRow + 1)). _
                                Find(What:="*", After:=.Rows(LastFilterRow + 1).Cells(1), _
                                SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
    If GetFilteredRangeBottomRow = LastFilterRow + 1 Then GetFilteredRangeBottomRow = 0
  End With
NoFilterOnSheet:
End Function

 

[keithkemble]

Peter,

I have added my code and the routine works a treat - even if there is no filtered data to clear.

Thanks again