Conditional Formatting, Delete Row with "" in A:A, Insert column

soccerjon1013

New Member
Joined
Apr 16, 2012
Messages
48
I should mention first, i'm new to the VBA and trying to learn, please excuse any of my rookie mistakes, and I greatly appreciate all of the help/input anyone is able to provide.

I have a report that we run daily. It involves pasting data from a database, removing rows with no Call Disposition, highlight row yellow from A-AL if Call Disposition is "Completed Survey", if any row in Q10 = "Yes" then insert column between AC and AD. There is a group of cells on tab "Totals Formatted" B21:b23 that reference the first tab "Daily Report Total" column AD. Since the column is inserted, it will change the reference from AD to AE. We would need to replace the reference in those cells to AD.

The conditional formatting would be a nice touch, but if I could have help with the rest of this, it would be help much.

I put in AL1 the formula =countif(AC:AC,"Yes")

There issues I'm having are as follow:
  1. Where it searches through A:A for cells with no call disposition, it looks like it is not selecting them as blank, perhaps I need to be looking for "" instead? What would be the right way to do so?
  2. It's not adding a column
  3. The steps to replace AE:AE with AD:AD give the formula a #REF value
  4. I have no idea where to start with the conditional formatting

Code:
Sub Install_Survey()'
' Install_Survey Macro
' Macro recorded 1/10/2015
'
' Keyboard Shortcut: Ctrl+q
'
' Sort 1st
    Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("AC2") _
        , Order2:=xlDescending, Key3:=Range("AD2"), Order3:=xlDescending, Header _
        :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
    
   ' Delete Empty Disposition
   Columns("A:A").Select
   Selection.SpecialCells(xlCellTypeBlanks).Select
   Selection.EntireRow.Delete
' Sort 2nd
    Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("AC2") _
        , Order2:=xlDescending, Key3:=Range("AD2"), Order3:=xlDescending, Header _
        :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
' Insert column if any issues
' Search for Yes for Q10


If AL1 > 0 Then
    Columns(29).Select
    Selection.Insert Shift:=xlToRight
    End If
If AL1 > 0 Then
' Replace Reason counts on last tab
    Sheets("Totals Formatted").Select
    Range("B21:B33").Select
    Selection.Replace What:="AE:AE", Replacement:="AD:AD", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="AE:AE", Replacement:="AD:AD", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Sheets("Daily Report Total").Select
    Range("A2").Select
    End If
End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

soccerjon1013

New Member
Joined
Apr 16, 2012
Messages
48
The only data that should be needed is in column A is the call disposition, column AC is Q10 header in AC1, with following rows will have Yes or No values
 

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
633
Office Version
  1. 365
Platform
  1. Windows
Hello. You could try this modified code and see if we would get some progress with it. Please try it on a copy of your workbook.


Code:
Sub Install_Survey() '
' Install_Survey Macro
' Macro recorded 1/10/2015
'
' Keyboard Shortcut: Ctrl+q
'
   ' Delete Empty Disposition
   Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
' Sort just once
    Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("AC2") _
        , Order2:=xlDescending, Key3:=Range("AD2"), Order3:=xlDescending, Header _
        :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
' Insert column if any issues
' Search for Yes for Q10

If Range("AL1").Value > 0 Then
    Columns(29).Insert
 ' Replace Reason counts on last tab
    With Sheets("Totals Formatted")
      .Range("B21").Formula = "=AD21+10" 'replace with your correct fórmula
      .Range("B21").AutoFill .Range("B21:B33")
    End With
 End If
End Sub
 

soccerjon1013

New Member
Joined
Apr 16, 2012
Messages
48
Hello. You could try this modified code and see if we would get some progress with it. Please try it on a copy of your workbook.


Code:
Sub Install_Survey() '
' Install_Survey Macro
' Macro recorded 1/10/2015
'
' Keyboard Shortcut: Ctrl+q
'
   ' Delete Empty Disposition
   Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
' Sort just once
    Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("AC2") _
        , Order2:=xlDescending, Key3:=Range("AD2"), Order3:=xlDescending, Header _
        :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
' Insert column if any issues
' Search for Yes for Q10

If Range("AL1").Value > 0 Then
    Columns(29).Insert
 ' Replace Reason counts on last tab
    With Sheets("Totals Formatted")
      .Range("B21").Formula = "=AD21+10" 'replace with your correct fórmula
      .Range("B21").AutoFill .Range("B21:B33")
    End With
 End If
End Sub


I get a run time error 1004, no cells are found. If it means anything, I would get that with my previous code, however if I would select the cells that didn't have anything, press delete and rerun the macro, I would not get that error. When I try that with this code however, I keep getting the error.
 

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
633
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Do you have formulae in column "A" that show blank?
Let's try with a Loop on column "A" searching for blank cells.

please replace this line
Code:
Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

by these
Code:
Dim Cellrng As Range
   For Each Cellrng In Range("A1", Range("A" & Rows.Count).End(xlUp))
    If Cellrng.Value = "" Then Cellrng.EntireRow.Delete
   Next Cellrng

[/CODE]
 

soccerjon1013

New Member
Joined
Apr 16, 2012
Messages
48
That did not give me the error, does allow for the new column to be added, however it does not delete the rows that are empty in column A.

Also, it looks like when it does the replace in cells b21:b33 i was only trying to replace the text in the formula, or reference from AE:AE to the new AD:AD, it looks like you have it replacing something else all together.
 

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
633
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

...however it does not delete the rows that are empty in column A.
Could you post a sample of your workbook?

Also, it looks like when it does the replace in cells b21:b33 i was only trying to replace the text in the formula, or reference from AE:AE to the new AD:AD, it looks like you have it replacing something else all together.
I posted a formula as an example of inserting formulae. You should replace it with your real formula. I think that's better than trying to replace addresses on existing formulae. But for this you should show me the formulae you were trying to replace addresses.
 

soccerjon1013

New Member
Joined
Apr 16, 2012
Messages
48
I see, the formula I'm using is: =COUNTIF('Daily Report Total'!AD:AD,D21), and of course it goes down the list to D33, each row.
 

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
633
Office Version
  1. 365
Platform
  1. Windows
1. There are invisible dirts at A127:A132 in sheets "Daily Report Total" that's why the code doesn't delete that rows; select that range and type Delete to fix it

2. I'm confused if you want insert a column at column 29:
a) if AL1>0 as is in your original code or
b) if there's any "Yes" in column "Q" or
c) Q10 = "Yes" (if this is the criteria it should be before or after sort the
table?)
I left the first condition but I think that not makes sense.

3. See if the code below works like you need, except for the doubt above, and then we'll work on Conditional Formatting ( highlight row yellow from A-AL if Call Disposition is "Completed Survey")


Code:
Sub Install_Survey() '
' Install_Survey Macro
' Macro recorded 1/10/2015
'
' Keyboard Shortcut: Ctrl+q
'
' // Delete Empty Disposition
  Dim LastRow As Long, LastColumn As Long
  With Sheets("Daily Report Total")
   LastRow = .UsedRange.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
   LastColumn = .UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    On Error Resume Next
    .Range("A1", .Range("A" & LastRow)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'// Sort
    With .Range(.Cells(2, "A"), .Cells(LastRow, LastColumn))
     .Sort Key1:=.Range("A2"), Order1:=xlAscending, Key2:=.Range("AC2") _
        , Order2:=xlDescending, Key3:=.Range("AD2"), Order3:=xlDescending, Header _
        :=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
    End With
  End With

' Insert column if any issues
' Search for Yes for Q10

  If Sheets("Daily Report Total").Range("AL1").Value > 0 Then
     Sheets("Daily Report Total").Columns(29).Insert
    'Replace Reason counts on last tab
    With Sheets("Totals Formatted").Range("B21:B33")
     .Replace What:="AE", Replacement:="AD", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End With
  End If

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,271
Messages
5,635,215
Members
416,847
Latest member
inaramos

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
Top