Newbie needs help with VBA (row selecting and deleting) 1

[calihiker]

I need to delete two rows at a time starting with row 4 and then skip a row and delete the next two rows etc. So I need to delete rows 4 & 5, 7 & 8, 10 & 11 and so on... I created the following code, but it is giving me an error saying "method range failed.." Any help would be very appreciated. Here's my code.

Sub Macro1()
Dim i As Integer

For i = 4 To 2000
Range("i:i + 1&quot.Select
i = i + 2
Next i

Selection.Delete Shift:=xlUp
End Sub


The reason I need to do this is I need to import a file that is in excel into an access database, but the records in excel are wrapped so that about half of each record is wrapped over to the next row.

for example..

LISTNAME CLIENT QTY SHIP USE
Offer Type Order # Mail Date


I copied and pasted the whole range side by side and then deleted a row of cells above the second set to line up the data, now I need to delete the extra data lines that exist and what to find out a easy way to do it in code. If anyone has any better ideas to combine the two rows into one, that would be great!!

Thanks

 

[Zathras]

Here's a simple routine that deletes all blank rows:
[blue]

Sub DeleteBlankRows()
Dim r As Range
Dim i As Long
  Set r = ActiveSheet.UsedRange.Rows(1)
  For i = 1 To ActiveSheet.UsedRange.Rows.Count
    If WorksheetFunction.CountA(r) = 0 Then
      Set r = r.Offset(1, 0)
      r.Offset(-1, 0).EntireRow.Delete
    Else
      Set r = r.Offset(1, 0)
    End If
  Next i
  Set r = Nothing
End Sub

[/color]
If you cut and paste (rather than copy and paste) then the row should be blank and this routine should do what you need.

 

[Stretchwickster]

Modify your macro to read like this:

  Dim i As Integer
    
  For i = 4 To 500
    Rows(i).Delete
    Rows(i).Delete
  Next i

Hope this helps!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[SkipVought]

Hi,

Start from the bottom and work up...

Sub testtt()
    Dim i As Integer
    
    For i = 30 To 4 Step -3
        Rows(i & ":" & i - 1).Delete Shift:=xlUp
    Next i
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Hi,

Start from the bottom and work up...

Sub testtt()
    Dim i As Integer
    
    For i = 2000 To 4 Step -3
        Rows(i & ":" & i - 1).Delete Shift:=xlUp
    Next i
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[calihiker]

Thanks for the replies It helped alot, I got it to work.