If condition else do nothing

[dshafique]

Hey everyone, I trying to figure out a formula for vba, where it checks to see if the the second row is blank.

basically

if row 2 is blank, do nothing
else, carry out program.

Writing a monthly report, so some months there's no data in the table, and just want to make sure it doesnt carry out the program an crash cause there's nothing

 

[DushiPunda]

Not sure if this will fit in with the rest of your code, but it should get you started:

If Application.CountA(ActiveSheet.Rows(2))=0 Then Exit Sub

What I mean by that is, I'm currently referring to the sheet using ActiveSheet which, obviously, implies that the sheet you want to check is the active sheet. Hopefully that makes sense. Depends on your code, but this will likely need to be one of the first lines. Let me know if you have any other questions.

 

[dshafique]

i think thats in the right direction, instead of exit sub, can I have it continue with the rest of the code? for example, the shet is called "New apps"

so

[COLOR=#333333]If Application.CountA("New Apps".Rows(2))=0 Then [/COLOR]
 
move on to the rest of the code, etc.

 

[DushiPunda]

Yep absolutely possible, but I don't know what the rest of your code looks like. Here is the basic premise:

If Application.CountA(Worksheets("New Apps").Rows(2))=0 Then

Code to run if Row 2 IS blank
​

Else

Code to run if Row 2 IS NOT blank
​

End If

If you provide me with your code, or at least a sample of it, I can give you a more definitive answer.

Edit: The code box was displaying significantly smaller than the code I provided, so I've removed the code tags so you can see everything without having to scroll through a tiny box.

 

[dshafique]

lol, so there's a lot of code here,

Sub sort()'
' sort Macro
' Daoud Shafique
' 7/25/17
' Revision 10
 
    Dim MyRange As Range
    Dim xColIndex As Integer
    Dim xRowIndex As Integer
    Dim myField As Long, myHdr As String
    
    Sheets("Availability by application").Select
    Range("A1").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name = "TableAll"
    Range("TableAll[[#Headers],[App Cat Id]]").Select
    ActiveCell.Offset(1).Select
    xIndex = Application.ActiveCell.Column
    xRowIndex = Application.ActiveSheet.Cells(Rows.Count, xIndex).End(xlUp).Row
    Range(Cells(2, xIndex), Cells(xRowIndex, xIndex)).Select
        With Selection
            .NumberFormat = "0"
            .Value = .Value
        End With
    
    Sheets("prior month").Select
    Range("A1").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name = "TablePrior"
    Range("TablePrior[[#Headers],[App Cat Id]]").Select
    ActiveCell.Offset(1).Select
    xIndex = Application.ActiveCell.Column
    xRowIndex = Application.ActiveSheet.Cells(Rows.Count, xIndex).End(xlUp).Row
    Range(Cells(2, xIndex), Cells(xRowIndex, xIndex)).Select
        With Selection
            .NumberFormat = "0"
            .Value = .Value
        End With
    
    Sheets("Availability by application").Select
    Range("TableAll[[#Headers],[Fiscal YTD]]").Select
    ActiveCell.Offset(1, 1).Select
    Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
    
    
    
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
       , Formula1:="=$E2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=$E2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 192
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("TableAll[[#Headers],[SLA Target]]").Select
    ActiveCell.Offset(1).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 2
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
    
    
    Range("TableAll[[#Headers],[Fiscal YTD]]").Select
    ActiveCell.Offset(1, 1).Select
    Selection.Copy
    Sheets("prior month").Select
    Range("TablePrior[[#Headers],[Fiscal YTD]]").Select
    ActiveCell.Offset(1, 1).Select
    Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats
    
    Application.CutCopyMode = False


   
    Sheets("Availability by application").Select
    Range("TableAll[[#Headers],[SLA Target]]").Select
    ActiveCell.Offset(1).Select
    Selection.Copy
    Sheets("prior month").Select
    Range("TablePrior[[#Headers],[SLA Target]]").Select
    ActiveCell.Offset(1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats
    
 
    Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "New Apps"
    
    Sheets("Availability by application").Select
    Range("TableAll[[#Headers],[Application Name]]").Select
    Range("TableAll[#All]").Select
    Selection.Copy
    Sheets("New Apps").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "keep"
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP([@[App Cat Id]],TablePrior[[App Cat Id]:[App Cat Id]],1,0)), ""Keep"", ""delete"")"
    myHdr = "keep"
With ActiveSheet.ListObjects("TableAll5").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="delete"
End With
    Range("TableAll5[#Data]").Select
    Selection.EntireRow.Delete
    ActiveSheet.ListObjects("TableAll5").AutoFilter.ShowAllData
    Range("TableAll5[keep]").EntireColumn.Hidden = True
    Range("A1").Select
    Columns.AutoFit
    Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "Deleted Apps"
    
    Sheets("prior month").Select
    Range("TablePrior[#All]").Select
    Selection.Copy
    Sheets("Deleted Apps").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "keep"
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP([@[App Cat Id]],TableAll[[App Cat Id]:[App Cat Id]],1,0)), ""Keep"", ""delete"")"
    myHdr = "keep"
With ActiveSheet.ListObjects("TablePrior6").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="delete"
End With
    Range("TablePrior6[#Data]").Select
    Selection.EntireRow.Delete
    ActiveSheet.ListObjects("TablePrior6").AutoFilter.ShowAllData
    Range("TablePrior6[keep]").EntireColumn.Hidden = True
    Range("A1").Select
    Columns.AutoFit
    
    
    
  
    
    
    Sheets("Availability by application").Select
    Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "Status"
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP([@[App Cat Id]],Apps[[App Cat Id]:[Application Name]],2,0)), ""Not Payment"", ""Payment"")"
    
    
     Sheets("prior month").Select
    Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "Status"
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP([@[App Cat Id]],Apps[[App Cat Id]:[Application Name]],2,0)), ""Not Payment"", ""Payment"")"
    
    
    Sheets("Availability by application").Select
    
    Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "Payment Apps"
    Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "New Payment Apps"
    Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "Deleted Payment Apps"
    
    
    Sheets("Availability by application").Select
    
    
    myHdr = "Status"
With ActiveSheet.ListObjects("TableAll").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="Payment"
End With


Range("TableAll[#All]").Select
    Selection.Copy
    Sheets("Payment Apps").Select
    Range("A1").Select
    ActiveSheet.Paste
    Columns.AutoFit
    
    Sheets("Payment Apps").Select
     Range("A1").Select
    Set Rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Rng, , xlYes)
    tbl.Name = "PayAll"
    tbl.TableStyle = "TableStyleLight10"
    


    
    Sheets("Availability by application").Select
     myHdr = "Status"
With ActiveSheet.ListObjects("TableAll").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="Payment"
End With
    Range("TableAll[#All]").Select
    Selection.Copy
    Sheets("New Payment Apps").Select
    Range("A1").Select
    ActiveSheet.Paste
    Columns.AutoFit
   Range("A1").Select
   Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
    Set Rng = Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Rng, , xlYes)
    tbl.Name = "PayNew"
    tbl.TableStyle = "TableStyleLight10"
    Range("B1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "keep"
    ActiveCell.Offset(1).Select
Selection.FormulaArray = _
        "=IFERROR(IF(ISERROR(INDEX(TablePrior,MATCH(1,(TablePrior[[App Cat Id]:[App Cat Id]]=[@[App Cat Id]])*(TablePrior[[Status]:[Status]]=[@Status]),0),2)),""keep"",""delete""),""error"")"
   myHdr = "keep"
With ActiveSheet.ListObjects("PayNew").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="delete"
End With
    Range("PayNew[#Data]").Select
    Selection.EntireRow.Delete
    ActiveSheet.ListObjects("PayNew").AutoFilter.ShowAllData
    Range("A1").Select
    
    
    
    Sheets("prior month").Select
  
     myHdr = "Status"
With ActiveSheet.ListObjects("TablePrior").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="Payment"
End With
    Range("TablePrior[#All]").Select
    Selection.Copy
    Sheets("Deleted Payment Apps").Select
    Range("A1").Select
    ActiveSheet.Paste
    Columns.AutoFit
    Range("A1").Select
    Set Rng = Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Rng, , xlYes)
    tbl.Name = "PayDeleted"
    tbl.TableStyle = "TableStyleLight10"
    
    Range("B1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "keep"
    ActiveCell.Offset(1).Select
Selection.FormulaArray = _
        "=IFERROR(IF(ISERROR(INDEX(TableAll,MATCH(1,(TableAll[[App Cat Id]:[App Cat Id]]=[@[App Cat Id]])*(TableAll[[Status]:[Status]]=[@Status]),0),2)),""keep"",""delete""),""error"")"
   myHdr = "keep"
With ActiveSheet.ListObjects("PayDeleted").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="delete"
End With
    Range("PayDeleted[#Data]").Select
    Selection.EntireRow.Delete
    ActiveSheet.ListObjects("PayDeleted").AutoFilter.ShowAllData
    Range("A1").Select
    
    
    
    
    
    
    
    Range("A1").Select
    Sheets("prior month").Select
    ActiveSheet.ListObjects("TablePrior").AutoFilter.ShowAllData
    Range("A1").Select
    Sheets("Availability by application").Select
    Range("TableAll[Status]").EntireColumn.Hidden = True
    Range("TablePrior[Status]").EntireColumn.Hidden = True
    Range("PayAll[Status]").EntireColumn.Hidden = True
    Range("PayNew[Status]").EntireColumn.Hidden = True
    Range("PayNew[keep]").EntireColumn.Hidden = True
    Range("PayDeleted[Status]").EntireColumn.Hidden = True
    Range("PayDeleted[keep]").EntireColumn.Hidden = True


    Sheets("prior month").Select
    Range("A1").Select
    Sheets("New Apps").Select
    Range("A1").Select
    Sheets("Deleted Apps").Select
    Range("A1").Select
    Sheets("New Payment Apps").Select
    Range("A1").Select
    Sheets("Deleted Payment Apps").Select
    Range("A1").Select
    Sheets("Availability by application").Select
     ActiveSheet.ListObjects("TableAll").AutoFilter.ShowAllData
    Range("A1").Select
    sFName = Application.GetSaveAsFilename
    If sFName <> "False" Then ActiveWorkbook.SaveAs sFName
    
End Sub

I was thinking of adding it to the very very end. what i want it to do is, if any of the pages have blank second rows (means no new apps or old apps that month) then dont do anything,
if row 2 does have content, then run the following program

Range("TableAll[[#Headers],[Fiscal YTD]]").Select    ActiveCell.Offset(1, 1).Select
    Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
    
    Dim cl As Range
    For Each cl In Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
    If cl >= Cells(cl.Row, "E") Then
       cl.Interior.Color = 5287936
    End If
    Next cl
    
    Range("TableAll[[#Headers],[Fiscal YTD]]").Select
    ActiveCell.Offset(1, 1).Select
    Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
    
    For Each cl In Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
    If cl < Cells(cl.Row, "E") Then
       cl.Interior.Color = 192
    End If
    Next cl

 

[DushiPunda]

So, to be clear:
1. You want to run the all of the first giant chunk of code no matter what
2. Then, if row 2 is not blank, run the second chunk of code
3. Repeat #2 for each worksheet??? Repeat #2 for specific worksheets??? Only run #2 for one specific worksheet???

Also, for what it's worth, your code would be significantly shorter & tidier (and probably a bit faster) if you:
1. Used tabs appropriately.
2. Stopped using .Select (would also reduce the need for ActiveSheet, ActiveCell, etc.)

That being said, let me know if what I said above is true and give me the correct response for #3 and I should be able to help you out.

Also, how are you initiating this macro? Is it bound to a button that you click?

 

[DushiPunda]

Here's an example of what I was saying regarding the use of .Select and whatnot:

your original code:

    Sheets("Availability by application").Select
    Range("A1").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name = "TableAll"
    Range("TableAll[[#Headers],[App Cat Id]]").Select
    ActiveCell.Offset(1).Select
    xIndex = Application.ActiveCell.Column
    xRowIndex = Application.ActiveSheet.Cells(Rows.Count, xIndex).End(xlUp).Row
    Range(Cells(2, xIndex), Cells(xRowIndex, xIndex)).Select
        With Selection
            .NumberFormat = "0"
            .Value = .Value
        End With

After the changes (although: untested, so it may not work as is):

Sheets("Availability by application").ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name = "TableAll"
xIndex = Range("TableAll[[#Headers],[App Cat Id]]").Offset(1).Column
xRowIndex = Sheets("Availability by application").Cells(Rows.Count, xIndex).End(xlUp).Row

With Range(Cells(2, xIndex), Cells(xRowIndex, xIndex))
    .NumberFormat = "0"
    .Value = .Value
End With

12 lines down to 7 lines (8 if you include the blank line), no .Select, no ActiveSheet/ActiveCell, etc. And that's just the first "section" of your code...Imagine if you did that to the entire thing! Anyway, that's not what you are here for so, I digress.

 

[dshafique]

lol, i'm still learning how to write macros/vba, greatly appreciate the shortening part. shorter is always better

in regards to your other questions, for number 3, i want that on most of the worksheets.

here's a breakdown of what the macros does, (ideally)

input: current month and previous month application data

1.) compare current month and previous month, and spit out any new apps (non existent in the old month) and any deleted apps (non existent in the current month) in a separate sheet.
2.) some of the apps from the current and previous month are apps that require payment, so find all the payment apps, and do the same, show all of the payment apps in one page, and new and old for them as well.
3.) the second chunk of code is for highlighting, the apps show monthly percentages (fdont know what for, i think target rate, not sure), have to make it so the apps in each month which dont meet the target turn red, and are green if they're fine. that's what the second chunk was for.

lol, ik its a lot of stuff, been on it for a couple weeks at least trying to fix one thing or another (i dont really know any vba as you can see lol)

 

[dshafique]

Thanks for all your effort, i finally figured it out lol
realized that if i left it blank, it would exit the loop, this is exactly what i wanted, thanks!

If Application.CountA(ActiveCell.EntireRow) = 0 Then
    Else
        Range("TablePrior[[#Headers],[Fiscal YTD]]").Select
        ActiveCell.Offset(1, 1).Select
        Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
        
        For Each cl In Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
        If cl >= Cells(cl.Row, "E") Then
           cl.Interior.Color = 5287936
        End If
        Next cl
        
        Range("TablePrior[[#Headers],[Fiscal YTD]]").Select
        ActiveCell.Offset(1, 1).Select
        Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
        
        For Each cl In Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
        If cl < Cells(cl.Row, "E") Then
           cl.Interior.Color = 192
        End If
        Next cl
    End If