count blank cells in a range using VBA

[divuraj]

Hi,

 

[Peter_SSs]

Hi,

Hi to you too! What's your question?

 

[VoG]

Guessing

Sub test()
MsgBox WorksheetFunction.CountIf(Range("A1:A100"), "")
End Sub

 

[Peter_SSs]

Well, for my guess then:

Sub test()
  MsgBox WorksheetFunction.CountBlank(Range("A1:A100"))
End Sub

 

[divuraj]

Hi,

i have few blank cells in column A. i find the last row containing data in column A and then i am trying to find the blank cells in column A which works fine.

once i find a blank cell in column A, i would need to select the range of cells till i find the cell with values,basically count the number of blank cells within a range.

now within this range i have values in column E, i need to find Min of these values and delete all blank rows which are not Min.

for eg, i identified first blank cell in A72, so i go to E71 and select the range of cells in E column till i find a non blank cell in A column which is E71:E72 in this case. i have 296 in E71 and 359 in E72, so i find the Min which is 296, so i delete the row which has 359.

Hope this explains.

please help.

Thanks,
divuraj.

 

[Peter_SSs]

for eg, i identified first blank cell in A72, so i go to E71 and select the range of cells in E column till i find a non blank cell in A column which is E71:E72 in this case. i have 296 in E71 and 359 in E72, so i find the Min which is 296, so i delete the row which has 359.

Hope this explains.

It's still not clear to me. For example,

a) if your first blank cell is A72, why are you looking in E71?

b) in your example, if E71 was 359 and E72 was 296, would you have deleted row 71 - that is a row that had data in column A?

Perhaps it might help if you could explain which rows below should be deleted, and why.

Excel Workbook
	A	B	C	D	E
69	1				300
70	1				299
71	1				296
72					359
73	1				380
74	1				250
75	1				275
76					399
77					100
78					222
79					301
80	1				600
81	1				450
82					450
83					450
84	1				444
divuraj