PDA

View Full Version : Solved: find blanks= 0



walkerke2
03-10-2009, 03:40 PM
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

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

MaximS
03-10-2009, 03:57 PM
can you post few examples of missed rows as this works fine for me?

walkerke2
03-10-2009, 04:03 PM
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
03-10-2009, 04:25 PM
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
03-11-2009, 01:34 AM
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
03-14-2009, 06:02 AM
can you paste a sample workbook?

walkerke2
03-15-2009, 07:52 AM
See Attached for sample

mdmackillop
03-15-2009, 08:10 AM
Try
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

walkerke2
03-15-2009, 09:46 AM
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
03-15-2009, 09:52 AM
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
03-15-2009, 10:36 AM
Magic thanks again MD, I will mark this thread as solved and hope to meet here again in the future.

Cheers
Kenny