Deleting every other cell automatically

[Blyss]

When i open a file there is certain data that repeatedly appears. My current method is to type in each row individually and making macros to delete it for me Is there an easier way? I believe the rows i want to delete occur every 28 rows or so and i want to delete the 4 rows after that.
What i've got now is something like:

Rows("44:55").Select
Selection.ClearContents
Selection.Delete Shift:=xlUp
Rows("86:97").Select
Selection.Delete Shift:=xlUp
Rows("125:136").Select
Selection.Delete Shift:=xlUp

etc...

This is getting tedious and i'm nowhere near the end...
is there a better way? please enlighten me
Thanks
BLyss

 

[xlbo]

Every 28 rows or so or EXACTLY every 28 rows - it makes a BIG difference

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[SkipVought]

BLyss,

You stated

I believe the rows i want to delete occur every 28 rows [red]or so[/red] and i want to delete the 4 rows after that.

You can't program for [red]or so[/red] conditions.

Do the rows that you want to delete contain ANYTHING?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Blyss]

Sorry i did mean 28 rows...
and the rows contain some gibberish which i want to get rid of.

oops...

 

[SkipVought]

What does your last 8 rows of data look like?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Blyss]

My last 8 rows of the spreadsheet have some data, sub totals and totals in them. No gibberish.
I was thinking of making the macros search for the gibberish and delete it instead... but i'm not quite sure how to write that.
Or perhaps there's a way of deleting the data before i import it into excel? It's originally in a .txt format.

 

[SkipVought]

The devil is in the details!

I need to see a sample of where your data ends, INCLUDING stuff that's BELOW.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Blyss]

try this code:


Option Explicit
Sub testme01()

Dim myCell As Range
Dim delRng As Range
Dim wks As Worksheet
Dim NumberOfRowsToDelete As Long

NumberOfRowsToDelete = 12

Set wks = Worksheets("sheet1")

With wks
For Each myCell In .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
If LCase(myCell.Value) = "" & "" & "" Then
If delRng Is Nothing Then
Set delRng = myCell.Resize(NumberOfRowsToDelete)
Else
Set delRng _
= Union(myCell.Resize(NumberOfRowsToDelete), delRng)
End If
End If
Next myCell
End With

If delRng Is Nothing Then
'do nothing
Else
delRng.EntireRow.Select
'delRng.EntireRow.Delete
End If

End Sub

 

[SkipVought]

You stated...

try this code:

To whom were you directing this imperative?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Blyss]

myself, hehe...just figured it...

in case anyone else was looking for something like this

 

[SkipVought]

It is generally UNRELIABLE to DELETE Entire Rows from the TOP DOWN.

It is more reliable to run your loop from the BOTTOM UP.

Check Search for the myriad of examples of Deleting Rows.

:-0

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[xlbo]

specifically this one:

thread707-875101

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question