SKIP CODE IF ROWS IN COLUMN IS 0

N

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:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
1594193179098.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
318
kevin9999
K
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
166
shknbk2
S
P
  • Question
Help with Vba Code
Replies
3
Views
160
Coyotex3
C
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
964
Joe4
Joe4
N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
184
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,944
Messages
6,122,384
Members
449,080
Latest member
Armadillos

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