Delete Blank Rows and Loop or Do While

[Chris Waller]

I am using Excel 2013 and I need some help on a couple of my Spreadsheet.

I have blocks of data down the spreadsheet that are separated with a blank row. The data is of a variable length but currently the blocks stretch from A1 to approximately J1000 I would like to be able to delete the blank rows between the blocks of data and secondly on one of the spreadsheets there is some data from Column A to J, however the data in columns A to D appear only on the first row of each block of data.

As this information applies to the block of data concerned, this needs to be copied to all the rows within that block etc to the end of the data. I have tried creating a macro, however, I do not know how to make it loop or do while. Any help would be appreciated.

Thanks Again

Chris Waller

 

[Fazza]

hi, Chris. maybe something like below. The on error resume next is to avoid errors if no blank cells are found

Sub back_up_first()

    On Error Resume Next
    
    With ActiveSheet.UsedRange.Columns("A:D")
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
    
    Columns("E").SpecialCells(xlCellTypeBlanks).EntireRow.Delete


End Sub

 

[Chris Waller]

Fazza, Thanks for your prompt response. I thought there was a problem somewhere along the line as some of the text appeared "red" and some was left aligned and others were right aligned, however, I found that all the formatting was already on the worksheet. Thanks once again.

 

[Fazza]

you're welcome. regards