SKIP CODE IF ROWS IN COLUMN IS 0

nasirpm

New Member
Joined
Jul 8, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
Sub MULTIPLE_OPTION_MAC()

'
   
    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Security AC ID"
    Range("C2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2],12)"
    countrow = Application.WorksheetFunction.CountIf(Range("A:A"), "<>")
    Selection.AutoFill Destination:=Range("C2:C" & countrow)
    Range("C2:C" & countrow).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("C:C").EntireColumn.AutoFit
    Range("D1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=4, Criteria1:=">0", _
        Operator:=xlAnd
    Range("C1:D1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

     
    Range("H1").Select
    ActiveSheet.Paste
    Columns("H:H").EntireColumn.AutoFit
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=4
    Range("E1").Select
    ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=5, Criteria1:=">0", _
        Operator:=xlAnd
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    Range("L1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
   
    Range("M1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=5
    Cells.Select
    Selection.AutoFilter
    Cells.Select
    Cells.EntireColumn.AutoFit
   
    ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=6
    Range("F1").Select
    ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=6, Criteria1:=">0", _
        Operator:=xlAnd
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
   
    Range("P1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("F1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
   
    Range("Q1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=5
    Cells.Select
    Selection.AutoFilter
    Cells.Select
    Cells.EntireColumn.AutoFit
   
   
    ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=7
    Range("G1").Select
    ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=7, Criteria1:=">0", _
        Operator:=xlAnd
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
   
    Range("T1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("G1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
   
    Range("U1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("$A$1:$G$" & countrow).AutoFilter Field:=7
    Cells.Select
    Selection.AutoFilter
    Cells.Select
    Cells.EntireColumn.AutoFit
   
   
   
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "OPT SEQ"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("N2").Select
    Selection.Copy
    Range("M2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("N2").Select
    Application.CutCopyMode = False
    countrowopt2 = Application.WorksheetFunction.CountIf(Range("L:L"), "<>")
    Selection.AutoFill Destination:=Range("N2:N" & countrowopt2)
   
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "OPT SEQ"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("J2").Select
    Selection.Copy
    Range("I2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("J2").Select
    Application.CutCopyMode = False
    countrowopt1 = Application.WorksheetFunction.CountIf(Range("H:H"), "<>")
    Selection.AutoFill Destination:=Range("J2:J" & countrowopt1)
    Range("J2:J" & countrowopt1).Select
   
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "OPT SEQ"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("R2").Select
    Selection.Copy
    Range("Q2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("R2").Select
    Application.CutCopyMode = False
    countrowopt3 = Application.WorksheetFunction.CountIf(Range("P:P"), "<>")
    Selection.AutoFill Destination:=Range("R2:R" & countrowopt3)
    Range("R2:R" & countrowopt1).Select
   
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "OPT SEQ"
    Range("V2").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("V2").Select
    Selection.Copy
    Range("U2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("V2").Select
    Application.CutCopyMode = False
    countrowopt4 = Application.WorksheetFunction.CountIf(Range("T:T"), "<>")
    Selection.AutoFill Destination:=Range("V2:V" & countrowopt4)
    Range("V2:V" & countrowopt1).Select

    Range("K1").Select
    ActiveCell.FormulaR1C1 = "CR"
    Range("K2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-3]&RC[-1]"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("K2:K" & countrowopt1)
    Range("K2:K" & countrowopt1).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "CR"
    Range("O2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-3]&RC[-1]"
    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O" & countrowopt2)
    Range("O2:O" & countrowopt2).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "CR"
    Range("S2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-3]&RC[-1]"
    Range("S2").Select
    Selection.AutoFill Destination:=Range("S2:S" & countrowopt3)
    Range("S2:S" & countrowopt1).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
       
    Range("W1").Select
    ActiveCell.FormulaR1C1 = "CR"
    Range("W2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-3]&RC[-1]"
    Range("W2").Select
    Selection.AutoFill Destination:=Range("W2:W" & countrowopt4)
    Range("W2:W" & countrowopt1).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
    Range("H2:J" & countrowopt1).Copy
    Windows("FILECRO V11_TEST1.csv").Activate
    Range("A2").Select
    ActiveSheet.Paste
   
    Windows("OPTION TEST MAC.xlsm").Activate
   
    Range("K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("FILECRO V11_TEST1.csv").Activate
    Range("E2").Select
    ActiveSheet.Paste
   
    Windows("OPTION TEST MAC.xlsm").Activate
   
    Range("L2:N" & countrowopt2).Copy
    Windows("FILECRO V11_TEST1.csv").Activate
    Range("A2").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
   
    Windows("OPTION TEST MAC.xlsm").Activate
   
    Range("O2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("FILECRO V11_TEST1.csv").Activate
    Range("E2").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
   
    Windows("OPTION TEST MAC.xlsm").Activate
   
    Range("P2:R" & countrowopt3).Copy
    Windows("FILECRO V11_TEST1.csv").Activate
    Range("A2").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
   
    Windows("OPTION TEST MAC.xlsm").Activate
   
    Range("S2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("FILECRO V11_TEST1.csv").Activate
    Range("E2").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
   
    Windows("OPTION TEST MAC.xlsm").Activate
   
    Range("T2:V" & countrowopt3).Copy
    Windows("FILECRO V11_TEST1.csv").Activate
    Range("A2").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
   
    Windows("OPTION TEST MAC.xlsm").Activate
   
    Range("W2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("FILECRO V11_TEST1.csv").Activate
    Range("E2").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
   
          
   
    Columns("B:B").Select
    Selection.NumberFormat = "0"
    Range("B2").Select
    Columns("A:E").EntireColumn.AutoFit
     
    MsgBox ("done")
   

End Sub



THIS MACRO WAS CREATED TO PULL OUT DATA FROM ANOTHER EXCEL WITH OPTIONS....4 OPTION....CAN I USE IT FOR DATA WITH 3 OPTIONS ....HOW CAN I SKIP COLUMNS IF NO VALUE?
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

nasirpm

New Member
Joined
Jul 8, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
1594193179098.png
 

nasirpm

New Member
Joined
Jul 8, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
THIS HOW THE PRIMARY DATA LOOKS LIKE.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,478
Messages
5,548,268
Members
410,825
Latest member
Dave12
Top