VBA Filter by non blank

[Bablu]

Hi All-

I wrote a piece of vba, but need a little help finishing it. Basically, its suppose filter by non blank rows on last column and copy to a separate sheet. But I am having problem with line ActiveSheet.Range("13:65536").AutoFilter Field:=19, Criteria1:="<>". Is there anyway I can make the field universal? Its Filed:=19 that is causing the problem.

Thanks.

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Break Sheet"

Set ws1 = Worksheets("Break Sheet")

Set ws2 = Worksheets("RCN_RECON")

Worksheets("RCN_RECON").Select

Cells(13, Columns.Count).End(xlToLeft).Select

ActiveSheet.Range("13:65536").AutoFilter Field:=19, Criteria1:="<>"

ws2.UsedRange.Copy Destination:=ws1.Range("A1")

 

[GlennUK]

What do you mean "make the field universal"?

Also, did you consider using Advanced Filter, and copy to your destination range directly?

 

[Bablu]

Basically, I want to filter by non blank cells on activecell.column. That is all.

 

[GlennUK]

Did you try using ActiveCell.Column instead of 19?

 

[Bablu]

Thats it. Thank you so much.



Bablu

 

[Peter_SSs]

You don't need to select anythig (selecting slows your code) and you could make more use of the variables you have defined. Try this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Filter_Copy()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws1 <SPAN style="color:#00007F">As</SPAN> Worksheet, ws2 <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> col <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    col = ActiveCell.Column<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Break Sheet"<br>    <SPAN style="color:#00007F">Set</SPAN> ws1 = Worksheets("Break Sheet")<br>    <SPAN style="color:#00007F">Set</SPAN> ws2 = Worksheets("RCN_RECON")<br>    <SPAN style="color:#00007F">With</SPAN> ws2<br>        Intersect(.UsedRange, .Rows("13:" & .Rows.Count)) _<br>            .AutoFilter Field:=col, Criteria1:="<>"<br>        .UsedRange.Copy Destination:=ws1.Range("A1")<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>

 

[tiedied61]

I realize I am posting to a very old thread, but it seemed the appropriate place to put this information.

Something I just discovered today about filtering for blanks using VBA code. Be sure to include this in ALL code where you need to show/hide blank cells:

' Get Rows with blanks

WorkRange.AutoFilter Field:=1, Criteria1:="=", Operator:=xlOr, Criteria2:="=" & ""

' Hides Rows with blanks ... same idea with the "<>" for operator

WorkRange.AutoFilter Field:=1, Criteria1:="<>", Operator:=xlOr, Criteria2:="<>" & ""

The first criteria gets true blank cells and those cells with hidden/non-printable characters, the 2nd criteria gets those cells containing an empty string. The Excel user-interface handles this nicely, but VBA code requires both criteria.

This undocumented caveat just cost me several hours of debugging, not to mention a few choice words from my manager about "I thought we were removing the blanks from these columns..."

Just thought I would share, in the hopes of saving you all some headaches.