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

 

[josros60]

Hi,

tried your sample wont' work since column H is no blank have formulas but no value, see sample of sheet:

NCL_AP STATEMENT1
	A
16
STATEMENT

 

[Peter_SSs]

see sample of sheet:

You need to select the whole range that you want to display before you click Mini-Sheet

 

[josros60]

NCL_AP STATEMENT1.xlsx
	A	B	C	D	E	F	G	H	I
15	INVOICE DATE	INVOICE #	INVOICE DUE DATE	A/C	AMOUNT$ INVOICE/PAYMENT/ ADJUSTMENT	REF/ CHQ. #		BALANCE	REMARKS
16		Balance brought forward
17	12/17/2020	CA1000100498	01/16/2021		339.00			339.00
18	12/17/2020	CA1000100499	01/16/2021		840.15			1,179.15
19	12/17/2020	CA1000100500	01/16/2021		7,775.15			8,954.30
20	01/14/2021	PY00000000000000004546			(8,954.30)			0.00
21
22
23
24
25
26
27
28
29
30
31
STATEMENT

Cell Formulas
Range	Formula
H17:H20	H17	=IF(AND(ISBLANK(E17)),"",H16+E17)

 

[josros60]

Sample here:

NCL_AP STATEMENT1.xlsx
	A	B	C	D	E	F	G	H	I
15	INVOICE DATE	INVOICE #	INVOICE DUE DATE	A/C	AMOUNT$ INVOICE/PAYMENT/ ADJUSTMENT	REF/ CHQ. #		BALANCE	REMARKS
16		Balance brought forward
17	12/17/2020	CA1000100498	01/16/2021		339.00			339.00
18	12/17/2020	CA1000100499	01/16/2021		840.15			1,179.15
19	12/17/2020	CA1000100500	01/16/2021		7,775.15			8,954.30
20	01/14/2021	PY00000000000000004546			(8,954.30)			0.00
21
22
23
24
25
26
27
28
29
30
31
STATEMENT

Cell Formulas
Range	Formula
H17:H20	H17	=IF(AND(ISBLANK(E17)),"",H16+E17)

 

[Peter_SSs]

Sample here:

There are no rows in that sample that I can see that meet the requirement you originally stated:

How can delete blank rows from a range that contains formulas but have no value

What row(s) do you want deleted from that sample and why?

 

[Zot]

There are no rows in that sample that I can see that meet the requirement you originally stated:



What row(s) do you want deleted from that sample and why?

Confused me too

 

[josros60]

The ones following the rows with data.

For example my statement have 60 rows range A17:h60 if I used 20 rows want the macros to delete rest the blanks rows in this case 40 rows but when macro runs udsint counta won't delete because on column H have formulas but no value.

 

[Rick Rothstein]

If I understand your setup correctly, this single line of code should delete the rows you want to delete...

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

 

[Peter_SSs]

If I understand your setup correctly, this single line of code should delete the rows you want to delete...

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

Perhaps you missed post #10?

@josros60
Are you sure that you tried the post #10 code?
If you have and it did not do what you want, can you post another mini-sheet that includes some extra rows like you are describing in words but so far not including in your mini-sheet?

 

[josros60]

any help?

want delete blank rows for A17:I80, let say I used rows A17 to I30 want to delete the rest blank rows even though in column H there are formulas but have no values from A30 to I80.

thanks.