Delete Blank Rows-I know I know its been asked a million>

Bigzippy

Board Regular
Joined
Jul 1, 2003
Messages
182
Sub DelBlankRows()

On Error Resume Next
range("B6:B194").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

this does not work but it describes what im tring to do,m imust not del anything above row 6

Please help Lal
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Works for me. What error do you get? Is that the ENTIRE Code??
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Yep, there's nothing wrong with the code you posted.

You can see any errors it's causing by removing the line: On Error Resume Next.

(Are the "blank" cells truely blank or are they formulas returning blanks?)
 

Bigzippy

Board Regular
Joined
Jul 1, 2003
Messages
182
formulas return blanks then copy and pastespecial values so it is blank as faras i can tell
 

Bigzippy

Board Regular
Joined
Jul 1, 2003
Messages
182
wierd isblank=false and info=text but i cant see anything in formula bar
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
can you post the formula that returns blanks? maybe it's not REALLY Blank...
 

Bigzippy

Board Regular
Joined
Jul 1, 2003
Messages
182
Sub addDates()
Application.ScreenUpdating = False
Dim i As Integer
Dim z As Integer
Dim StartingDate As Variant

'ActiveSheet.ShowAllData
range("A6:A3000").Select
Selection.ClearContents

StartingDate = Sheet1.range("stopdate").Value
Sheet1.range("A6").Value = Sheet1.range("stopdate").Value
z = Sheet1.range("tradingdays").Value
range("A7").Select
ActiveCell.Offset(-1, 1).FormulaR1C1 = "=+IF(WEEKDAY(RC[-1],2)<6,RC[-1],"""")"

For i = 1 To z
ActiveCell.FormulaR1C1 = "=+R[-1]C-1"
ActiveCell.Offset(0, 1).FormulaR1C1 = "=+IF(WEEKDAY(RC[-1],2)<6,RC[-1],"""")"
ActiveCell.Offset(1, 0).Select
Next i

'change to numbers(values)
range("A6").Select
range(Selection, Selection.End(xlToRight)).Select
range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
range("A6").Select

End Sub
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
You can try using the autofilter. . .
Code:
With ActiveSheet
  With Range("B6:B192")
    .AutoFilter Field:=1, Criteria1:="=***", Operator:=xlAnd
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
  End With
  .AutoFilterMode = False
End With

Does that help?

[EDIT:]
(Corrected the range to start at row 6)
 

Forum statistics

Threads
1,181,102
Messages
5,928,061
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top