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

[soccerjon1013]

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

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

 

[soccerjon1013]

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]

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.

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]

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.

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]

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

Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

by these

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]

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]

...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]

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.

 

[soccerjon1013]

You should be able to view the file here: https://drive.google.com/file/d/0B7XvBcGgEagIc0ZPV3BFRU5MS1k/view?usp=sharing

 

[Osvaldo Palmeiro]

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")

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