Particular Range is Empty or not check

[Abhijeet]

Hi

I want to identify particular range row wise if any value then copy that row not entire row but range of that row want to copy and paste in sheet4 please tell me how to do this

Range is N2:T100

This code is checking whole Row i want to check in particular range

Sub T1()
Dim row As Range
Dim sheet As Worksheet
Set sheet = ActiveSheet

For i = 1 To sheet.UsedRange.Rows.Count

    Set row = sheet.Rows(i)
    If WorksheetFunction.CountA(row) = 0 Then
        MsgBox "row " & i & " is empty"
    End If

Next i
End Sub

 

[Marc L]

Hi !

Dim {Range variable} As Range​

For Each {Range variable} In {Source Range}.Rows

If Application.CountA({Range variable}) Then {Range variable}.Copy {Destination}
…​

 

[Abhijeet]

Hi
I tried but not work this please tell me

Sub T1()
Dim row As Range
Dim sheet As Worksheet
Set sheet = ActiveSheet

For Each row In Range("A1:D5").Rows

    Set row = sheet.Rows(i)
    If Application.CountA(row) Then row.Copy
        MsgBox "row " & i & " is empty"
    End If

Next i
End Sub

 

[Chihiro]

Why are you setting row = sheet.Rows(i)?

With Marc's code, you are not even using i...

Remove that line.

 

[Abhijeet]

I am not doing this why End if given error i am not getting please tell me

 

[Chihiro]

What do you mean your are not doing this?

Sub T1()
Dim row As Range
Dim sheet As Worksheet
Set sheet = ActiveSheet

For Each row In sheet.Range("A1:D5").Rows
    Set row = sheet.Rows(i) 'This line needs to be removed
    If Application.CountA(row) Then 
        row.Copy 'You need to set paste destination here
    Else 'You need else or you are showing message when condition is met
        MsgBox "row " & i & " is empty" 
    End If
Next i 'This should not be i but row since you are looping each row
End Sub

 

[Marc L]

Sub T1()
Dim row As Range
Dim sheet As Worksheet
Set sheet = ActiveSheet

For Each row In Range("A1:D5").Rows

Set row = sheet.Rows(i)
If Application.CountA(row) Then row.Copy {Destination}
MsgBox "row " & i & " is empty"
End If

Next i
End Sub

 

[Abhijeet]

Thanks i got this now

 

[Monty]

What do you mean your are not doing this?

Sub T1()
Dim row As Range
Dim sheet As Worksheet
Set sheet = ActiveSheet

For Each row In sheet.Range("A1:D5").Rows
    Set row = sheet.Rows(i) 'This line needs to be removed
    If Application.CountA(row) Then
        row.Copy 'You need to set paste destination here
    Else 'You need else or you are showing message when condition is met
        MsgBox "row " & i & " is empty"
    End If
Next i 'This should not be i but row since you are looping each row
End Sub

Great piece of code.

 

[Abhijeet]

Hi

Please tell me when this macro run that time if any value find in this then apart from this range tow different column value want to paste please tell me

If Application.CountA(row) Then row.Copy