Application.CountA issue

[bsnapool]

Hi All

 'Check Range
                        'Get last row to check
                        lRowEnd = WS.Range("B" & Rows.Count).End(xlUp).Row
            
                        'Check for values in F:N
                        For R = 12 To lRowEnd
                            If LCase$(WS.Cells(R, "B").Text) <> "total" Then
                         For C = 6 To 12 'Cols F:N                              
                         If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) Then 'Copy row to Summary
    
                                        lRowTo = lRowTo + 1
                                        With wsSumm
                                            .Rows(lRowTo).Value = WS.Rows(R).Value
                                            .Cells(lRowTo, "A").Value = "Week " & sWeeks(iWeekPtr)
                                        End With
                                        Exit For
                                    End If

I have the above code which copies the row if any data is within columns F:L The current code only copies the line if there is any data, if null does not bring back anythin.

What I would Like...

I would like to extend the columns from F:N.
Any if there is no data within F:L and some within L:N, to still copy the row.

Is this possible?

Thanks in advance

Andrew

 

[bsnapool]

I know what this is....

It is stopping the totals on the source data spreadsheet being included in my summary sheet....

Any ideas why SUX - suspended without pay is being included in the summary?

 

[Von Pookie]

Got it, I think.

It's this line what's causing the actual trouble:

lRowEnd = WS.Range("B" & Rows.Count).End(xlUp).Row

This looks up the column from the last row of column B--and stops at the first cell with data that it finds. In the file you sent me, this would stop at row 222 (S037 SSP Sickness) and the "For R=12 to lrowend" loop would go until it hits that row.

Since I'm doubting you really want to look that far down the page, and you appear to have limited the number of rows, can't you just hard-code the rows to check?

Instead of this

For R = 12 To lRowEnd

You could just specify the rows:

For R = 12 To 133

and then not worry about the word "total" at all.

Alternately, I suppose you could just use the same coding you already have except find the last row by looking *down* column B, starting from B12:

lRowEnd = WS.Range("B12").End(xlDown).Row

If the only entry is in B12, this should return the row with the "Total" label in column B. Other than that, it would stop at the first blank cell. For example, you can select B12, and press Ctrl+down arrow to see what cells would be affected by using xlDown.

 

[bsnapool]

Kirsty

Spot on. Working like a dream now.

Cant thank you enough..

Many thanks

Andrew

 

[Von Pookie]

Yay, it's working!