VBA- Rows that has any data

[Ramy27]

I need to programatically count he number of ROWS that has any data in column A,C or D. (NOT no. of non-empty cells, but "non-empty" rows)

---------------------------------
Your help is much appreciated, R.
---------------------------------

 

[xlbo]

so what have you tried ?

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Ramy27]

If it was one column i would have tried CountA(A:A) or something...but here i haven't a clue, sorry

---------------------------------
Your help is much appreciated, R.
---------------------------------

 

[SkipVought]

Hi,

    Set rng = Intersect(Union(Range("A:A"), Range("C:D")), ActiveSheet.UsedRange)
    With rng
      lFirstRow = .Row
      lLastRow = lFirstRow + .Rows.Count - 1
      i = 1
      For lRow = lFirstRow To lLastRow
        If Application.CountA( _
            Intersect(rng, _
                Union(Cells(lRow, "A"), _
                    Cells(lRow, "C"), _
                    Cells(lRow, "D")))) > 0 Then
            cnt = cnt + 1
        End If
        i = i + 1
      Next
    End With

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Ramy27]

Hi, thanks for the code, but i'm getting the following error.
method intersect if '_Global' failed

---------------------------------
Your help is much appreciated, R.
---------------------------------

 

[xlbo]

You are aware that this is not a helpdesk and you should be able to try and debug code a bit yourself...???

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

You may need

application.intersect(....)

application.union(....)

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}