How about
VBA Code:
Sub HarshilMehta()
Dim Ary As Variant, Nary As Variant
Dim r As Long, c As Long, nr As Long, cc As Long
With Sheets(3)
Ary = .Range("A6:AM" & .Range("D" & Rows.Count).End(xlUp).Row).Value2
End With
ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
For r = 1 To UBound(Ary)
For c = 10 To UBound(Ary, 2)
If Ary(r, c) <> "" Then
nr = nr + 1
For cc = 1 To UBound(Ary, 2)
Nary(nr, cc) = Ary(r, cc)
Next cc
Exit For
End If
Next c
Next r
Sheets(1).Range("A1").Resize(nr, UBound(Ary, 2)).Value = Nary
End Sub
Sorry for late reply. This code worked well but what I am trying to do is copy the range
if condition <>"" is met then copy range A:AM of sheet3 to sheet 1 till the last cell of column D in sheet3 and then
reverse the condition to ="" and copy the copy range A:AM of sheet3 to sheet 1 till the last cell of column D in sheet3. I have pasted the code below for your reference.
Sub HarshilMehta()
Dim Ary As Variant, Nary As Variant
Dim r As Long, c As Long, nr As Long, cc As Long
With Sheets(3)
Ary = .Range("A7:AM" & .Range("D" & Rows.Count).End(xlUp).Row).Value2
End With
ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
For r = 1 To UBound(Ary)
For c = 10 To UBound(Ary, 2)
If Ary(r, c) <> "" Then
nr = nr + 1
For cc = 1 To UBound(Ary, 2)
Nary(nr, cc) = Ary(r, cc)
Next cc
Exit For
End If
Next c
Next r
Sheets(1).Range("A2").Resize(nr, UBound(Ary, 2)).Value = Nary
With Sheets(3)
Ary = .Range("A7:AM" & .Range("D" & Rows.Count).End(xlUp).Row).Value2
End With
ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
For r = 1 To UBound(Ary)
For c = 10 To UBound(Ary, 2)
If Ary(r, c) = "" Then
nr = nr + 1
For cc = 1 To UBound(Ary, 2)
Nary(nr, cc) = Ary(r, cc)
Next cc
Exit For
End If
Next c
Next r
Sheets(1).Range("A2").Resize(nr, UBound(Ary, 2)).Value = Nary
End Sub
The highlighted lines seems to b buggy.