[bmbewick]

Hi all,

I'm using Excel 2000 and have a sheet of data that includes a row with a cell value only every five cells. I want to take this data and create a new row on 'sheet 2'. I've attached a dummy excel sheet which should hopefully explain all. I've only ever created macros using the 'record' function which is obviously not ideal for this since the data I need to run this on includes 30,000 individuals. Any help in creating a macro for this purpose would be appreciated.

Thanks
bmb

 

[The Villan]

Here is an example

Sub RemoveBlanks()
'
' Copy sheet and Remove Blanks Macro
'
Sheets("Sheet1").Copy After:=Sheets(1)
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Range("A1").Select
End Sub

The manual way to do this is

Copy sheet1 as a new sheet
Click in cell A1 of the new sheet
Click on Edit, Goto
Click on Special in bottom left corner of dialogue box
select Blanks and click on OK
Click on Edit, Delete, Entire Row, OK

Job done

 

[bmbewick]

Cheers - I'll give this a go

 

[Zack Barresse]

No need to select...

Sub CopySheetWithoutBlanks()
    Sheets("Sheet1").Copy after:=Sheets("Sheet1")
    With ActiveSheet
        On Error Resume Next
        .Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub

 

[The Villan]

Trust you to upstage me Fireftyr

Long time since I did macro's and I did mine by the Run Macro method.

 

[Zack Barresse]

LOL! Sorry Villan, wasn't trying to upstage you. I just feel strongly about Selecting.