Deleting blank rows and have formulas but have no value

[josros60]

How can delete blank rows from a range that contains formulas but have no value using VBA, any sample please.

Thank you

 

[Zot]

Is it something similar to this?

VBA: Delete rows with no values.

Rick rothstein made this code to help me delete columns with no values in them: Sub DeleteAllBlankColumns() Dim LastRow As Long LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row On Error Resume Next With Range("A" & (LastRow + 1) &...

www.mrexcel.com

 

[Peter_SSs]

How big (number of rows) is your data?

Can you also clarify by referring to the sample below and assuming all cells contain formulas

• Are we only looking a a particular column, so if it was column C only rows 7 & 8 would be deleted, or
• Are we looking at all columns, so rows 3, 4, 6, 7 & 8 would get deleted, or
• By "blank rows" do you mean that only row 8 would be deleted, or
• Something else?

josros60.xlsm
	A	B	C	D
1	Hdr1	Hdr2	Hdr3	Hdr4
2	x	x	x	x
3	x		x	x
4	x		x	x
5	x	x	x	x
6	x		x	x
7	x	x		x
8
9	x	x	x	x
Sheet1

 

[josros60]

It's a statement of has 80 rows but sometimes use only 20 rows need delete the rest but one column have formulas .

Thank you

 

[Peter_SSs]

It's a statement of has 80 rows

.. and how many columns?
Does the data start in column A?

but one column have formulas .

Which column?

 

[josros60]

8 columns start A17

 

[Peter_SSs]

8 columns start A17

There was another question.

 

[josros60]

Sorry, missed it.
Yes, from you sample only row 8 even if there's a formula but no value. (my table has 8 columns with A:H, and H column has carry forward balance formula)

 

[josros60]

Any suggestion?

thank you,

 

[Peter_SSs]

delete blank rows from a range

my table has

Now I'm a little confused as to whether your data is in a formal Excel table (ListObject) or just a normal range that you are referring to as a "table".
Assuming a normal range & that column A can be used to determine the bottom of the 'real' data, try this.

Test with a copy of your workbook.

Sub Del_Rows()
  Range(Range("A" & Rows.Count).End(xlUp).Offset(1), Range("H" & Rows.Count).End(xlUp)).EntireRow.Delete
End Sub