Macro Help

[Randall Roberts]

Hoping some one can help me with this

in column a
i need a macro that will select
rows 2-13 15-26 28-29 etc for as far as i need it to go

Does anyone know of an easy way to do this

I am using excel xp
but this will also be used on excel 2000

Thanks for the help

 

[JohnI in Brisbane]

Randall,

Using a Helper column with a header, insert the following formula from Row
2:-

=MOD(ROW()-1,13)

Use "Data" - "Filter" - "Autofilter"
then click filter dropdown, select "(Custom...)", select "does not equal" 0
values.

select all the rows in the range.

Use "Edit" - "Go To...", click "Special...", select "Visible cells only",
click "OK".

You should have the rows selected that you want.

regards,

JohnI

 

[Don]

Randall,


Sub test()

Range ("A2:A13,A15:A26,A28:A29").Select

End Sub


Don Pistulka

 

[Randall Roberts]

OK

Maybe i wasnt clear enough here is what i am trying to do

I am going to import about 400 records into excel

each record has 12 rows
i want to remove all but the first row from each record
is there a better way to do this then recording a macro where i
manually select then delete all the rows i do not want

Thanks Again

 

[Don]

Randall

This macro will delete every 12th row starting from the last row with data
going up. That will leave you with rows 1,13,25,37, etc. I noticed that
these rows do not match the rows you asked for in your first post, so you
might want to change the "12" after Mod to delete some other number of rows,
if this is not exactly what you need.

Sub test()
Dim rng As Range
Range("a1").Activate
Set rng = Range(ActiveCell, ActiveCell.End(xlDown))
For i = rng.Rows.Count To 1 Step -1
counter = counter + 1
Cells(i, 1).Select
If counter Mod 12 <> 0 Then
Selection.EntireRow.Delete
Else
End If
Next i
End Sub

Don Pistulka

 

[Don]

The first sentence of the previous post should have read:

This macro will delete every 11 rows and leave the 12th row starting from
the last row with data in the range, going up.

Don Pistulka

 

[Don Guillett]

This will be very fast. If you do not have a header row, insert a row at
column A and put in a label of some kind.

Sub allbutone()'assumes header row and col A
Sheets("sheet2").Columns(1).Copy Sheets("sheet1").Columns(1)
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For i = 2 To lastrow Step 1
If Len(Cells(i, "a")) < 1 Then End
Cells(i + 1, 1).Resize(i + 11, 1).EntireRow.Delete
Next i
End Sub

 

[Don Guillett]

delete the first line. I used for testing.

 

[Don]

Hi Don,

I will grant you that this method is faster, however when I use a test
column of a header row and additional rows consisting of 1,2,3,4,etc., the
resulting numbers do not have a consistent number between them ( i.e. every
eleventh number). I think you need to start at the bottom of the range and
work up to get the desired results.

Don Pistulka

 

[Don Guillett]

Don

I think OP said 12 rows. I tested with a header row, 1 in 2-13, 2 in 14-25,
etc and it worked, as advertised. I can send you the workbook, if desired.

 

[Don]

Don,

No need to send. If the OP is happy, I'm happy.

Don Pistulka

 

[Randall Roberts]

Thanks this worked great
It wasnt practical to insert a header
as the other poster sugested

 

[Don Guillett]

Then use this that I just tested by changing the
for i = 2
to
for i = 1


Sub allbutone_NO_Header() 'delete row 2-12 for each selection
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For i = 1 To lastrow Step 1
If Len(Cells(i, "a")) < 1 Then End
Cells(i + 1, 1).Resize(i + 11, 1).EntireRow.Delete
Next i
End Sub

 

[Don]

You're Welcome

Don Pistulka

Thanks this worked great
It wasnt practical to insert a header
as the other poster sugested

 

[Randall Roberts]

using this i am not gitting only the first row of each section
i fi type down in column a 1-12 repeating this 5 times i should get
only 1s after the macro
but instead i get
1 2 4 7 11 4

 

[Don Guillett]

I tested with 1 in 1-12, 2 in 13-24, 3 in 25-36, etc

 

[Randall Roberts]

which would give you 1,2,3, when done but not neccasarly the first row
in eac set

what i need to do is remove rows 2-12 in every set leaving me only the
first row.
your macro seems to be doing something else

Thanks for the help sorry i couldnt use it

 

[Randall Roberts]

I found this to work nicely with one small issue

if there is a blank cell in the records it wants to only sort down
that far
is it possible to have it ignore blank cells?

Thanks
Randall

 

[Don Guillett]

Sorry I forgot about this.

Sub Thisistheway()'remove row 2-12,14-24 etc NO Header
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, 1).Resize(11, 1).EntireRow.Delete
Next
End Sub