effective error checking

D

dshafique

Board Regular
Joined
Jun 19, 2017
Messages
171
Hey guys, I am working with a sheet with about 10,000+ of data, and im trying to count and see how many blank rows are in 2 separate sheets using countblank

in one sheet, there are 1947 blanks in a given column, in the other sheet, there are 413 blanks in a given column.

however, when i combine the the two sheets into one, and then do countblank, it tells me there are 2071. there should be 2360 if you add the two previous values together.

i checked to see if the macros accidentally skipped rows when pasting, but the number of rows are the same. Even after running the macros a few times, i still get the same result of 2071.

is there a way to see what went wrong, and where? aside from going down and counting each of the blank rows (10,000+)
thanks!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Maybe if you post your code and explain what you want it to achieve, someone here can spot the problem.
 
Upvote 0
This is just one part of the whole project, Basically, the part i'm stuck in, I want to count the blank cells in a specific column. there are two sheets, and when i count blanks, it gives me an accurate count.
when i combine the two sheets (copy paste both sheet data to a 3rd sheet) and count the blanks, it gives me an off number. it gives me 2071 blanks instead of the 2036 blanks.


Code: 
ActiveCell.FormulaR1C1 = _        "=COUNTBLANK(combined[[GITRM confirmed remediation]:[GITRM confirmed remediation]])"
    
    
    Range("A1").Select

here's the whole code if that helps.

Code: 
Sub Vulnerability()'
' Vulnerability Macro
'


'
    Rows("1:3").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    Set Rng = Range(Range("A4"), Range("A4").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Rng, , xlYes)
    tbl.Name = "hmvt"
    tbl.TableStyle = "TableStyleLight8"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Total Number of Records"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(hmvt[[CIO_]:[CIO_]])"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Total Number Open"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTBLANK(hmvt[[GITRM confirmed remediation]:[GITRM confirmed remediation]])"
    Range("B3").Select
    Sheets("Backlog Reclassified").Select
    Rows("1:3").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Set Rng = Range(Range("A4"), Range("A4").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Rng, , xlYes)
    tbl.Name = "cht"
    tbl.TableStyle = "TableStyleLight8"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Total Number of Records"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(cht[[CIO_]:[CIO_]])"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Total Number Open"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTBLANK(cht[[GITRM confirmed remediation]:[GITRM confirmed remediation]])"
    Range("cht[[#Headers],[CIO_]]").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    
    ActiveCell.FormulaR1C1 = "Source2"
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "Reclassified"
    ActiveCell.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets("Backlog Internal Medium").Select
    Range("hmvt[[#Headers],[CIO_]]").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "Source2"
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "Internal Medium"
    ActiveCell.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select
    Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "Combined"
    Sheets("Backlog Internal Medium").Select
    ActiveSheet.ListObjects("hmvt").Range.Select
    Selection.Copy
    Sheets("Combined").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Set Rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Rng, , xlYes)
    tbl.Name = "combined"
    tbl.TableStyle = "TableStyleLight8"
    
    
    Range("A1").Select
    currentColumn = 1
    While currentColumn <= ActiveSheet.UsedRange.Columns.Count
        columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value
        keepColumn = False
        If columnHeading = "LTO" Then keepColumn = True
        If columnHeading = "STO" Then keepColumn = True
        If columnHeading = "Last Detected" Then keepColumn = True
        If columnHeading = "First Detected" Then keepColumn = True
        If columnHeading = "Updated Last Detected Date" Then keepColumn = True
        If columnHeading = "Key - IP-QID-Port" Then keepColumn = True
        If columnHeading = "IP" Then keepColumn = True
        If columnHeading = "ComputerName" Then keepColumn = True
        If columnHeading = "OS" Then keepColumn = True
        If columnHeading = "QID" Then keepColumn = True
        If columnHeading = "PORT" Then keepColumn = True
        If columnHeading = "AppCat" Then keepColumn = True
        If columnHeading = "Application_Name" Then keepColumn = True
        If columnHeading = "SERVERPURPOSE" Then keepColumn = True
        If columnHeading = "Remediation Approach" Then keepColumn = True
        If columnHeading = "CxO Planned Date" Then keepColumn = True
        If columnHeading = "GITRM confirmed remediation" Then keepColumn = True
        If columnHeading = "Comments" Then keepColumn = True
        If columnHeading = "NOTE" Then keepColumn = True
        If columnHeading = "Source2" Then keepColumn = True
        If keepColumn Then
            currentColumn = currentColumn + 1
        Else
            ActiveSheet.Columns(currentColumn).Delete
        End If
        If (ActiveSheet.UsedRange.Address = "$A$1") And (ActiveSheet.Range("$A$1").Text = "") Then Exit Sub
    Wend
    
    arrColOrder = Array("Source2", "LTO", "STO", "Last Detected", "First Detected", "Updated Last Detected Date", _
    "Key - IP-QID-Port", "IP", "ComputerName", "OS", "QID", "PORT", "AppCat", "Application_Name", _
    "SERVERPURPOSE", "Remediation Approach", "CxO Planned Date", "GITRM confirmed remediation", "Comments", _
    "NOTE")
    counter = 1
    Application.ScreenUpdating = False
    For ndx = LBound(arrColOrder) To UBound(arrColOrder)
        Set Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        If Not Found Is Nothing Then
            If Found.Column <> counter Then
                Found.EntireColumn.Cut
                Columns(counter).Insert Shift:=xlToRight
                Application.CutCopyMode = False
            End If
            counter = counter + 1
        End If
    Next ndx
    Application.ScreenUpdating = True
    Range("A1").Select
    Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "temp"
    Sheets("Backlog Reclassified").Select
    ActiveSheet.ListObjects("cht").Range.Select
    Selection.Copy
    Sheets("temp").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Set Rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Rng, , xlYes)
    tbl.Name = "temp"
    tbl.TableStyle = "TableStyleLight8"
    Range("A1").Select
    currentColumn = 1
    While currentColumn <= ActiveSheet.UsedRange.Columns.Count
        columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value
        keepColumn = False
        If columnHeading = "LTO" Then keepColumn = True
        If columnHeading = "STO" Then keepColumn = True
        If columnHeading = "Last Detected" Then keepColumn = True
        If columnHeading = "First Detected" Then keepColumn = True
        If columnHeading = "Updated Last Detected Date" Then keepColumn = True
        If columnHeading = "Key - IP-QID-Port" Then keepColumn = True
        If columnHeading = "IP" Then keepColumn = True
        If columnHeading = "ComputerName" Then keepColumn = True
        If columnHeading = "OS" Then keepColumn = True
        If columnHeading = "QID" Then keepColumn = True
        If columnHeading = "PORT" Then keepColumn = True
        If columnHeading = "AppCat" Then keepColumn = True
        If columnHeading = "Application_Name" Then keepColumn = True
        If columnHeading = "SERVERPURPOSE" Then keepColumn = True
        If columnHeading = "Remediation Approach" Then keepColumn = True
        If columnHeading = "CxO Planned Date" Then keepColumn = True
        If columnHeading = "GITRM confirmed remediation" Then keepColumn = True
        If columnHeading = "Comments" Then keepColumn = True
        If columnHeading = "NOTE" Then keepColumn = True
        If columnHeading = "Source2" Then keepColumn = True
        If keepColumn Then
            currentColumn = currentColumn + 1
        Else
            ActiveSheet.Columns(currentColumn).Delete
        End If
        If (ActiveSheet.UsedRange.Address = "$A$1") And (ActiveSheet.Range("$A$1").Text = "") Then Exit Sub
    Wend
    
    arrColOrder = Array("Source2", "LTO", "STO", "Last Detected", "First Detected", "Updated Last Detected Date", _
    "Key - IP-QID-Port", "IP", "ComputerName", "OS", "QID", "PORT", "AppCat", "Application_Name", _
    "SERVERPURPOSE", "Remediation Approach", "CxO Planned Date", "GITRM confirmed remediation", "Comments", _
    "NOTE")
    counter = 1
    Application.ScreenUpdating = False
    For ndx = LBound(arrColOrder) To UBound(arrColOrder)
        Set Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _
                          SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        If Not Found Is Nothing Then
            If Found.Column <> counter Then
                Found.EntireColumn.Cut
                Columns(counter).Insert Shift:=xlToRight
                Application.CutCopyMode = False
            End If
            counter = counter + 1
        End If
    Next ndx
    Application.ScreenUpdating = True
    
    Range("A1").Select
    ActiveSheet.ListObjects("temp").DataBodyRange.Select
    Selection.Copy
    Sheets("Combined").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("temp").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Combined").Select
    Range("combined[[#Headers],[Source2]]").Select
    ActiveCell.FormulaR1C1 = "Source"
    Rows("1:3").Select
    Selection.Insert Shift:=xlDown
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Total Number of Records"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(combined[[Source]:[Source]])"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Total Number Open"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTBLANK(combined[[GITRM confirmed remediation]:[GITRM confirmed remediation]])"
    
    
    Range("A1").Select
    
End Sub
 
Upvote 0
I checked to see if it's pasting extra stuff or leaving stuff out. Sorry i got the numbers mixed up. It should be 2360, but it gives me 2071
 
Upvote 0
Is it possible you're pasting into the blank spaces of the first sheet? How are you selecting your ranges to do the initial counts?
 
Upvote 0
so here's my process for all the counts

first sheet (returns 1947): =COUNTBLANK(hmvt[[GITRM confirmed remediation]:[GITRM confirmed remediation]])

Second Sheet (Returns 413): =COUNTBLANK(cht[[GITRM confirmed remediation]:[GITRM confirmed remediation]])

Combined Sheet( Returns 2071): =COUNTBLANK(combined[[GITRM confirmed remediation]:[GITRM confirmed remediation]])

hmvt is first table
cht is second sheet table
combined is the combined sheet table

 
Upvote 0
sorry for all the trouble guys, i figured out the problem, it was that one of the other columns in the other page was named differently. cause a bunch of issues.
 
Upvote 0
No problem, every day's a school day with VBA for me too, good to see you sorted it.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question Question
Excel VBA - Check For Duplicates and Return the Count on a Large Data Set
Replies
4
Views
403
Kaper
Kaper
J
  • Question Question
VBA Error on Logic to Remove Rows based on Blank Cells
Replies
4
Views
378
Alex Blakenburg
A
M
  • Question Question
VBA error not making sense
2
Replies
10
Views
237
Skyybot
Skyybot
R
  • Question Question
VBA for Cut, move, paste, delete rows
Replies
6
Views
305
RForster6
R
C
  • Solved
VBA - Checking for Duplicates
Replies
2
Views
881
cwhaley1
C

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top