Macro to delete Rows 1, 3 and 10
 

Hello All,
I am using Office XP
I have a group of data in Column A in set of 10 records separated by a blank
row.
Eg.
1
2
3
4
5
6
7
8
9
10
<blank row
11
....
....
20
<blank row
and so on so forth

I wish to delete Row Nos. 1, 3 and 10 from each group of 10 Records, so that
each new group would consist of only 7 Records each. I hope I have made
myself clear

Any help or suggestion to achieve this would be appreciated.
TIA
Rashid Khan

Macro to delete Rows 1, 3 and 10
 

Sub test()
Dim i As Long, rng As Range

With ActiveSheet
For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row Step 11
If rng Is Nothing Then
Set rng = Union(Rows(i), Rows(i + 2), Rows(i + 9))
Else
Set rng = Union(rng, Rows(i), Rows(i + 2), Rows(i + 9))
End If
Next

' Debug.Print rng.Address
rng.Delete xlShiftUp

End With
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Rashid Khan" wrote in message
...
Hello All,
I am using Office XP
I have a group of data in Column A in set of 10 records separated by a
blank
row.
Eg.
1
2
3
4
5
6
7
8
9
10
<blank row
11
...
...
20
<blank row
and so on so forth

I wish to delete Row Nos. 1, 3 and 10 from each group of 10 Records, so
that
each new group would consist of only 7 Records each. I hope I have made
myself clear

Any help or suggestion to achieve this would be appreciated.
TIA
Rashid Khan

Thanks - Macro to delete Rows 1, 3 and 10
 

Hi Rob,
Thanks a lot for your time and effort. It works lika a charm. You people
are really a genious lot.
Rashid
"Rob van Gelder" wrote in message
...
Sub test()
Dim i As Long, rng As Range

With ActiveSheet
For i = 1 To .Cells(Rows.Count, 1).End(xlUp).Row Step 11
If rng Is Nothing Then
Set rng = Union(Rows(i), Rows(i + 2), Rows(i + 9))
Else
Set rng = Union(rng, Rows(i), Rows(i + 2), Rows(i + 9))
End If
Next

' Debug.Print rng.Address
rng.Delete xlShiftUp

End With
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Rashid Khan" wrote in message
...
Hello All,
I am using Office XP
I have a group of data in Column A in set of 10 records separated by a
blank
row.
Eg.
1
2
3
4
5
6
7
8
9
10
<blank row
11
...
...
20
<blank row
and so on so forth

I wish to delete Row Nos. 1, 3 and 10 from each group of 10 Records, so
that
each new group would consist of only 7 Records each. I hope I have made
myself clear

Any help or suggestion to achieve this would be appreciated.
TIA
Rashid Khan