Dynamic CountBlank Hi All, Can anybody assist me to find out count blank :- 1) I

Question

Dynamic CountBlank

Hi All,

Can anybody assist me to find out count blank :-

1) I have 1 sheet name" Macro" . in this sheet we have heading description from range : N7:N13.

2) another sheet name " Final sheet" it contains the raw data after manipulating by macro. the raw data range starts from A7:V...... last row every time different.

but heading alway starts from A7:V7.

Now I want a formula/Macro that automatically calculate the count blank heading mentioned in sheet name "Macro".

Ex:
According to above given link the expected output for blank count should be as below:

UN-UPDATED FIELDS
WO500 Tx Supplier 8
WO500 TX Line Ordered Date 40
WO510 RFB Sent Date 89
WO520 Tx Connection (1st Part) Forecast 34
WO520 Tx Backhaul Completed Actual 81
WO530 Tx Connection (2nd Part) Forecast 67
WO530 Tx E2E Completed Actual 111

Thanks

Explanation / Answer

>>>>>>>>>T^ry this co9de...

[NEW Edit.....]

Sub CountBlankCells_01()

'Sep 03, 2014

Const N As Long = 8 '<< data (Final Sheet) starts from row 8, change
Const sh1Name As String = "Macro" '<< TARGET sheet
Const sh2Name As String = "Final Sheet" '<< SOURCE sheet
Const myCol As String = "O" '<< expected results in column O in sheet Macro
Const N1 As Long = 7 '<< export data, first row in Macro sheet
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets(sh1Name)
Set ws2 = Sheets(sh2Name)
Dim v As Variant
v = Array("I", "J", "N", "O", "P", "Q", "R") '<<export data from specific columns
Dim r As Long, x As Long
For x = 0 To UBound(v)
r = ws2.Cells(Rows.Count, v(x)).End(xlUp).Row
If IsError(ws2.Range(v(x) & N & ":" & v(x) & r).SpecialCells(xlCellTypeBlanks).Count) Then
ws1.Cells(x + N1, myCol).Value = 0
Else
ws1.Cells(x + N1, myCol).Value = ws2.Range(v(x) & N & ":" & v(x) & r).SpecialCells(xlCellTypeBlanks).Count
End If
Next
End Sub

XXXXXXXXXXXXXX

or

using COUNTBLANK formula

Sub CountBlankCells_02()

'Sep 03, 2014

Const N As Long = 8 '<< data (Final Sheet) starts from row 8, change
Const sh1Name As String = "Macro" '<< TARGET sheet
Const sh2Name As String = "Final Sheet" '<< SOURCE sheet
Const myCol As String = "O" '<< expected results in column O in sheet Macro
Const N1 As Long = 7 '<< export data, first row in Macro sheet
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets(sh1Name)
Set ws2 = Sheets(sh2Name)
Dim v As Variant
v = Array("I", "J", "N", "O", "P", "Q", "R") '<<export data from specific columns
Dim r As Long, x As Long
For x = 0 To UBound(v)
r = ws2.Cells(Rows.Count, v(x)).End(xlUp).Row
ws1.Cells(x + N1, myCol).Formula = "=COUNTBLANK('" & sh2Name & "'!" & v(x) & N & ":" & v(x) & r & ")"
Next
End Sub

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.