Macro to delete blank rows

[fable]

Hi guys!

I'm trying to use the macro in the thread below to erase the blank rows in my document - amongst other things. The top half is recorded, so please excuse any lack of coding eloquence!

Sub Macro1()
'
' Macro1 Macro
'

'
    Cells.Select
    Range("E28").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("1:8").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Columns("K:K").Select
      Selection.Replace What:=";", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    ActiveSheet.UsedRange.Select
'Deletes the entire row within the selection if the ENTIRE row contains no data.
Dim i As Long
'Turn off calculation and screenupdating to speed up the macro.
With Application
 .Calculation = xlCalculationManual
 .ScreenUpdating = False
'Work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
 Selection.Rows(i).EntireRow.Delete
End If
Next i
 .Calculation = xlCalculationAutomatic
 .ScreenUpdating = True
End With
        
        
        
  End Sub

The stages should be:
1 - select all
2 - Past Values
3 - Delete top 8 rows
4 - find replace blanks in column k for , & ;
5 - delete empty rows
6 - delete bottom 11 rows

Steps 1-4 are perfect. Steps 5 & 6 are vague/not present.
Step 5 Stumbles when because i think it still thinks there is content in the cells as if i 'clear contents' manually, then run the macro it works.
Step 6 I've yet to put in because i'm wondering if it needs to be a whole new step or if it can be integrated into step five by dictacting if column C is empty, then delete row.

Is anyone with some superior knowledge than my incredibly basic VBA able to help? I'd be really grateful! It'll save me a heck of a lot of time.

~Fable

 

[igold]

Hi,

In your step 5, do you only want to delete rows that have no data in them at all. If that is true, then are there certain columns that the data will only always be in (like column K) or might the data be in any column..

igold

 

[fable]

Hi igold,

Thanks very much for responding, the help's greatly appreciated. I can make either work, but deleting based on Column C being empty would be ideal as it would let me negate step 6

~Fable

 

[igold]

Hi fable,

This is very generic just to give you an idea. If you change the range to where your rows of Column C are, then this formula based on your needs should work for you...

[COLOR=#00007F]Dim[/COLOR] rng [COLOR=#00007F]As[/COLOR] Range
[COLOR=#00007F]Set[/COLOR] rng = Range("C1:C10").SpecialCells(xlCellTypeBlanks)
rng.EntireRow.Delete

HTH

igold