delete blank rows in all sheets in a certain range

[Marco1975]

Hi all,

I could use a macro that deletes all blank row in a range of all the sheets from the seventh sheet.


for example:

the seventh sheet then there are these sheets :


cagliari

milano1

milano2

torino

roma1

roma2

roma3


in each sheet for each row there is a number ranging from 1 to 200 .
The macro should delete blank row that are included between 1 and 200 for each sheet .
Obviously leaves filled to not be touched .

For example in cagliari the last row is full the n ° 80 , then should be deleted rows from 81 up to 200.

I do not know if I was clear, however, attach two examples one before using the macro that I would use and one after using the macro.

thank you.

 

[Marco1975]

i tried this:

Sub N()
Dim rng As Range
Dim N As Long
For N = 7 To ThisWorkbook.Worksheets.Count

Set rng = Range("c16:c215").SpecialCells(xlCellTypeBlanks)

rng.EntireRow.Delete

Next N
 
End Sub

but doesn't work...

 

[Marc L]

Hi,

it seems you forgot the worksheet before the range …
​

 

[Marco1975]

ok... thanks Marc now it works...

Sub N()
Dim rng As Range
Dim N As Long
For N = 7 To ThisWorkbook.Worksheets.Count

Set rng = Worksheets(N).Range("c16:c215").SpecialCells(xlCellTypeBlanks)

rng.EntireRow.Delete

Next N

     
 
End Sub

 

[Marc L]

Well done !

You must Set Rng = Nothing after Next to free ressource …

Better without this variable :​

Sub Demo()
For N& = 7 To Worksheets.Count
    Worksheets(N).Range("C16:C215").SpecialCells(xlCellTypeBlanks).Rows.Delete
Next N
End Sub

 

[Marco1975]

yes it's better... thanks

 

[John()]

Hi just finished this and noticed you dont seem to have sorted it out yet ...
Have tested it and it clears all the rows out on all the sheets from 7 on that are blank .... anyway try it out on a sample book like the one you uploaded and see if its suitable

Sub clearrows()
Dim CheckCell As String
Dim WS_count As Integer
WS_count = ActiveWorkbook.Worksheets.Count
Application.ScreenUpdating = False
For i = 7 To WS_count
Sheets(i).Select
num_rows = Sheets(i).Range("c16").End(xlDown).Row + 1

Range("b" & num_rows).Select
CheckCell = ActiveCell.Value
'the if checks if the cell with date is selected
'if it is then they are no rows to delete

If CheckCell = "date" Then
'if range hasnt blank rows leaves if goes to next loop
Else
Rows(num_rows & ":" & "215").Select
Selection.Delete Shift:=xlUp
'this just selects a single cell as line before selects
'all the rows to delete and keeps them selected as it loops

Range("b" & num_rows).Select
End If
Next i
Application.ScreenUpdating = True
End Sub

 

[Marco1975]

thank you very much john also this solution is great!!!!