Solved: find blanks= 0

[walkerke2]

Hi All,

Having problems as my filter is missing out on data on different sheets.

I always search for Blanks= 0 within my sheets to process the sheet. I find that some of my sheets have rows which have been missed by this filter??

Any help with a update on the code much appreciated.

Kenny

My Sheets have this type of data within
Mtrs = 56 Read=49 Skip= 7 Blanks= 0 Import=56 Mtrs = 58 Read=46 Skip= 8 Blanks= 4 Import=57

[vba]Sub WORKFLOW_COMPLETED_BOOKS()
Dim rng As Range
Dim sh As Worksheet
Dim target As Worksheet

Set sh = ActiveSheet
Set target = Worksheets.Add
With sh
'Please Check code is searching required column

Set rng = Range(.Range("R1"), .Range("R1").End(xlDown))
.Columns("R:R").AutoFilter Field:=1, Criteria1:="=** Blanks= 0**"

rng.EntireRow.Copy target.Range("A1")
.Columns("R:R").AutoFilter
ActiveSheet.Name = "WORKFLOW"

'To advise sheet created
MsgBox "WORKFLOW Sheet Created Click OK to Continue"

End With
End Sub[/vba]

[MaximS]

can you post few examples of missed rows as this works fine for me?

[walkerke2]

Hi MaximS,

This is the issue. The workbook is sent as CSV from source, it's recently I have found that it's not reliable and wondering if there is another way to search for this kind of string??

Kenny

[MaximS]

hmm if thats a csv i bet you have all in one line.
so if that's the case why not to open it as excel file (text to columns with space as delimitter) and do some joining as space is should not always be treated as delimitter. Then you can sort just one column.

Other option is capturing only numbers and putting them into right column (ie. "Mtrs = 5" will be 5 in column Mtrs).

[walkerke2]

Hi MaximS,

Tried that and I get the problem of 13Blanks= 0Import=99

This might be the issue of the new sheets having being missed with my autofilter code??

[MaximS]

can you paste a sample workbook?

[walkerke2]

See Attached for sample

[mdmackillop]

Try
[VBA]Sub WORKFLOW_COMPLETED_BOOKS()
Dim rng As Range
Dim sh As Worksheet
Dim target As Worksheet

Set sh = ActiveSheet
Set target = Worksheets.Add
With sh
'Please Check code is searching required column
Set rng = Range(.Cells(1, "R"), .Cells(Rows.Count, "R").End(xlUp))
.Columns("R:R").AutoFilter Field:=1, Criteria1:="*Blanks= 0*"
rng.EntireRow.Copy target.Range("A1")
.Columns("R:R").AutoFilter
ActiveSheet.Name = "WORKFLOW"

'To advise sheet created
MsgBox "WORKFLOW Sheet Created Click OK to Continue"

End With
End Sub
[/VBA]

[walkerke2]

Thanks MD, At the Same time can I add additional code to link to the column in case of change. I can then work on error checking too

As heading is Route_Totals but VB looks at this as an invalid heading

Columns(1).Name = "Route_Totals"
rng ("Route_Totals").select

Think you will know better??

Cheers

[mdmackillop]

If Route_Totals is the heading in the column you wish to Filter, I would do a Find on Row1 to return the Column Number, then use that in the code instead of "R"

[walkerke2]

Magic thanks again MD, I will mark this thread as solved and hope to meet here again in the future.

Cheers
Kenny