Insert Count Blanks at the last row

[teqtaq]

Hello everyone!
I am outputting data from Access into Excel using VBA.
It is simple table with ID and columns with partially missing data.

I need to output about 5 WKsheets.
Each will have different amount of columns and rows.

I need to insert total counting blanks per column.
There is a function for that but I can't see how to count last row and insert total right below.

ID___________Col

12___________Value2
11___________ValueNull
10___________Value1

Total 1 (counts blanks only)

Thanks

 

[henniec]

Hi,

I would suggest not to do the counting in Excel but to do the counting in Access via a query, I would assume that the data you are transferring is the result of a query and run another query to count the null or empty cells.

Write your VBA in such a way to copy the query to Excel and do a count of the total rows and insert the counted empty cells in a two or two below your last row.

Hope it works, if not come back and I will give more info.

Hennie

 

[teqtaq]

I had actually done so.
However I have to redo it for more info.
I am taking apart a whole table with missing data.
I need to count cells with missing data.
Inserting 1 to have a value. Counting 1s. Summarizing it.
It takes hours. I have large tables.

I thought using something like this would be easier.
If you don't think it is - please, tell why. Thanks


lngMaxRow = xlWsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lngMaxCol = xlWsh.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For r = 2 To lngMaxRow
xlWsh.Cells(r, lngMaxCol + 1).FormulaR1C1 = "=COUNTBLANK(RC1:RC" & lngMaxCol & ")"
Next r

For c = 1 To lngMaxCol
xlWsh.Cells(lngMaxRow +1, c).FormulaR1C1 = "=COUNTBLANK(R1C:R" & lngMaxRow & "C)"
Next c

 

[SkipVought]

Hi,

Write your VBA in such a way to copy the query to Excel and do a count of the total rows and insert the counted empty cells in a two or two below your last row.

I guess that your exported queryies are REPORTS, to be housed in Excel.

I make it a 100% practice to put all my table aggregations AT THE TOP. Why make the user hunt for the bottom of the report to get to see "the bottom line"?

If you put the table aggregations in row 1 and the table headings in row 3, you can maintain some level of consistency and keep the table isolated from the aggregations.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[teqtaq]

I agree, SkipVough, however my client doesn't.

I would like to find out how to make code to calculate blanks per row and column.

 

[henniec]

Hi,

I did a quick query in Access using a table of 25 rows and about 8 columns. Checked the number of empty cells and adjusted it not to have equal numbers.

Ran a query using the Sigma button and do a count on the table. The count function seem to count only cells with numbers. First column was a date.

Ran a second query using the date count as the total number of rows and subtracted the other columns to get the number of blank cells per column. This query can be used for your empty cells.


Skip I agree with you its easier at the top.

Hennie

 

[SkipVought]

Its not only easier, but it makes sense. Aggregations at the BOTTOM, is a throwback to pencil, paper and adding machine, and many people have not stopped to think about what they are doing. "Its the way we have ALWAYS done it!" Well, get a rock and chisel!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[teqtaq]

I am probably not explaining this correctly.
I am running code and having an error at the :

lngMaxRow = xlWsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row

It says 'subscript out of range'.

Can you please, take a look and tell me why. I can't find an error. THANKS



Private Sub Color_Cells()
Dim xlApp As Object
Dim xlWbk As Object
Dim xlWsh As Object

Dim lngMaxRow As Long
Dim lngMaxCol As Long
Dim r As Long
Dim c As Long


Set xlApp = CreateObject(Class:="Excel.Application")
Set xlWbk = xlApp.Workbooks.Open(FileName:=strReportName)

For Each xlWsh In xlWbk.Worksheets

xlWsh.UsedRange.FormatConditions.Add Type:=1, _
Operator:=3, Formula1:="="""""
With xlWsh.UsedRange.FormatConditions(1).Borders
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105

End With




lngMaxRow = xlWsh.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
lngMaxCol = xlWsh.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For r = 2 To lngMaxRow
xlWsh.Cells(r, lngMaxCol + 1).FormulaR1C1 = "=COUNTBLANK(RC1:RC" & lngMaxCol & ")"
Next r

For c = 1 To lngMaxCol
xlWsh.Cells(lngMaxRow + 1, c).FormulaR1C1 = "=COUNTBLANK(R1C:R" & lngMaxRow & "C)"
Next c


xlWsh.Range("A11").Font.Bold = True
xlWsh.UsedRange.Font.Size = 8.5
xlWsh.UsedRange.Columns.AutoFit
xlWsh.UsedRange.FormatConditions(1).Interior.ColorIndex = 36
Next xlWsh


xlWbk.Close SaveChanges:=True
xlApp.Quit
Set xlWsh = Nothing
Set xlWbk = Nothing
Set xlApp = Nothing

End Sub

 

[teqtaq]

I was missing Excel Library references! YAHOOO!

 

[henniec]

Hi,

I copied your code and modified it slightly to step through the code. The first time through gave no problem but the second time I had an error at the same spot as you but received a different error code. Mine was "Run-time error '91. Object vairable or with block viarable not set."

You need to add a With just before "For Each xlWsh In xlWbk.Worksheets" and With end after "Next xlWsh"

Something else you need to do is to set your worksheet and most likely activate your worksheet so you can move from the one worksheet to the next in your workbook. That should happen after your "For each".

Not sure exactly of the code but that seems to be the solution.

Hennie

 

[SkipVought]

with xlWsh.UsedRange
  lngMaxRow = .row + .rows.count - 1
  lngMaxCol = .column + .columns.count - 1
End with

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[teqtaq]

Thank you gentlemen. You halped a lot. I am almost done with this piece.

I have one little thing I need to do.

First Wksheet is going to be formatted differently then all.

When I use following order it gives me an error 'for without nest etc.' What is the problem here? Thanks

For Each xlWsh In xlWbk.Worksheets
IF xlWsh.Name <> "FirstWorkheet"

*****************code

End IF
Next xlWsh