HOW CAN I DEFINE A RANGE IN LOOP
 

Hi

I wanted to delete empty rows from excel sheet in particualar range for
which i wrote a below code and succeeded.

Now i want to delete only row data of A to G column instead of deleting full
row but i am unable to define it in the loop. I have also wrote besides the
code that where i am not able to define.

Please help me somebody to solve it out. I will be really very thankful for
it.
------------------------------------------------
Sub DelEmptyRow()
Dim I As Variant
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i= 1 to rng step 1
If ActiveCell.Value = "" Then
Selection.EntireRow.Delete 'HERE I WANT TO DEFINE I VALUE AND DONT WANT TO
DELETE THE FULL ROW BUT A(I) TO G(I)
'Selection.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Select
End If
Next I
Application.ScreenUpdating = True
End Sub
-----------------------------------------------------------------
Please mail me at

Thanks.
Best Regards.
Didar.

HOW CAN I DEFINE A RANGE IN LOOP
 

My interpretation:

Sub DelEmptyRow()
Dim rng As Range, rng2 As Range, c As Range

Set rng = Intersect(Selection.EntireRow, Columns(1))
Application.ScreenUpdating = False
For Each c In rng.Cells
If c.Value = "" Then
If rng2 Is Nothing Then Set rng2 = c.Resize(1, 7) Else _
Set rng2 = Union(rng2, c.Resize(1, 7))
End If
Next c
rng2.Delete Shift:=xlUp
Application.ScreenUpdating = True
End Sub

Regards,
Greg

"Didar." wrote:

Hi

I wanted to delete empty rows from excel sheet in particualar range for
which i wrote a below code and succeeded.

Now i want to delete only row data of A to G column instead of deleting full
row but i am unable to define it in the loop. I have also wrote besides the
code that where i am not able to define.

Please help me somebody to solve it out. I will be really very thankful for
it.
------------------------------------------------
Sub DelEmptyRow()
Dim I As Variant
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i= 1 to rng step 1
If ActiveCell.Value = "" Then
Selection.EntireRow.Delete 'HERE I WANT TO DEFINE I VALUE AND DONT WANT TO
DELETE THE FULL ROW BUT A(I) TO G(I)
'Selection.Delete Shift:=xlUp
Else
ActiveCell.Offset(1, 0).Select
End If
Next I
Application.ScreenUpdating = True
End Sub
-----------------------------------------------------------------
Please mail me at

Thanks.
Best Regards.
Didar.

HOW CAN I DEFINE A RANGE IN LOOP
 

Correction to my post:

To avoid an error in case no cells are blank then change the line:
rng2.Delete Shift:=xlUp
To:
If Not rng2 Is Nothing Then rng2.Delete Shift:=xlUp

Regards,
Greg

"Greg Wilson" wrote:

My interpretation:

Sub DelEmptyRow()
Dim rng As Range, rng2 As Range, c As Range

Set rng = Intersect(Selection.EntireRow, Columns(1))
Application.ScreenUpdating = False
For Each c In rng.Cells
If c.Value = "" Then
If rng2 Is Nothing Then Set rng2 = c.Resize(1, 7) Else _
Set rng2 = Union(rng2, c.Resize(1, 7))
End If
Next c
rng2.Delete Shift:=xlUp
Application.ScreenUpdating = True
End Sub

Regards,
Greg