Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to delete rows | Excel Discussion (Misc queries) | |||
Macro to delete rows | Excel Discussion (Misc queries) | |||
My Macro Won't Delete Rows?? | New Users to Excel | |||
Delete all Rows Macro | Excel Discussion (Misc queries) | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming |