Application.CountA issue

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
Hi All

Code:
 '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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
I think this line of code is the issue, but dont know how to amend it so it meets my requirements... Any ideas?



Code:
 For C = 6 To 12 'Cols F:N                              
                         If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12)))
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Well, cross-posting isn't going to do you any favors. The general rule is to at least *mention* that you have posted the same question on (an)other board(s) with a link.

Like one to your post on Ozgrid, here:
http://www.ozgrid.com/forum/showthread.php?t=57211

(For the record, Ozgrid follows that same "rule"--though they tend to enforce them a bit more firmly there. In short, it would be polite to add a link to your post here to the post at Ozgrid).
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
As to the question at hand: You appear to be using COUNTA correctly to count the number of blank cells, however your IF statement itself may be the issue.

Code:
If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) Then...

If the result of COUNTA is what? The IF statement is looking for an expression that returns true or false. COUNTA simply returns a number. In English, what you've basically ended up with here is "If 3 (result of the counta), then..."

I would specify the result you want to check for, personally:
Code:
If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) = 0 Then...
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452

ADVERTISEMENT

Von Pookie

Apologies.. I am really desperate to get this to work as I have a dealine to meet...

I tried your suggestion but that just bring back all the data even if there is no data what so ever within the row...

Any other suggestion?
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686

ADVERTISEMENT

What I posted was just an example, really. You do not want to copy if there is no data, correct? Setting the CountA to "if...=0 then" in the context of the macro in your original post will do the opposite--it will copy when there is no data found in the range.

So, you just change it.
Code:
If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) > 0 Then...

Note the greater than sign in place of the =. If the CountA result is 0, no data was found in the range. If it returns anything greater than 0, there is data.
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
My current code would bring back this row:
AEA - AtkinsonGroveCFB.xls
BCDEFGHIJKLMNOPQRS
15MoniqueSerranoM129255514/08/2006YSICS034
MasterEntry


I would also like to bring back this example to. Even there is no numeric data as seen below, I would still like to bring back the other data, such as date, and sickness codes:
AEA - AtkinsonGroveCFB.xls
ABCDEFGHIJKLMNOPQRS
154MoniqueSerranoM129214/08/2006YSICS034
MasterEntry
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Oh, wow. I appear to have missed a key point of your first post, there:
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.
Looks like I probably went from the second post on. Sorry about that. :oops:

Right, ok, then.

Untested, but you could try:
Code:
If Application.CountA(WS.Range(WS.Cells(R, 6), WS.Cells(R, 12))) > 0 Or Application.CountA(WS.Range(WS.Cells(R, 12), WS.Cells(R, 14))) > 0 Then...
 

bsnapool

Active Member
Joined
Jul 10, 2006
Messages
452
Thanks for your quick reply Von.

Your code brings back the following: When I only want the 1 row of data.

[HTML removed by admin]
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,126
Messages
5,857,523
Members
431,883
Latest member
Hien

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top