Excel Macro ??

  dobie2 18:49 10 Feb 2010


I have a spreadsheet full of data, 16000+ lines which I need to manipulate.

I need one of two solutions to the problem.

1 Is it possible to delete every even numbered row easily.

2. Or, is it possible to add all the even numbered rows of data to the end of the odd numbers.



  VoG II 19:13 10 Feb 2010

1. is easier

Sub DelEven()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = ActiveSheet.UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
For i = LR To 2 Step -1
If i Mod 2 = 0 Then Rows(i).Delete
Next i
Application.ScreenUpdating = True
End Sub

  VoG II 19:44 10 Feb 2010


Sub MoveEven()
Dim LR As Long, i As Long, ws1 As Worksheet, ws2 As Worksheet
Application.ScreenUpdating = False
Set ws1 = ActiveSheet
Set ws2 = Worksheets.Add
With ws1
LR = .UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
For i = 2 To LR
If i Mod 2 = 0 Then .Rows(i).Cut Destination:=ws2.Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
ws2.UsedRange.Copy Destination:=.Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

No need to scan sketches into your computer with Moleskine's new smart pen

How to use 3D Touch on iPhone

Comment importer des contacts d’un iPhone à un autre iPhone ?