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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
1594193179098.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA autofilling code
Replies
6
Views
603
Peter_SSs
Peter_SSs
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
273
rlv01
R
E
  • Question Question
Cleaning up some "Select" code
2
Replies
17
Views
265
EssKayKay
E
M
  • Solved
Tidy up simple code?
Replies
9
Views
88
MOB
M
Dazzawm
  • Solved
Change Range To Recorded Macro
Replies
3
Views
191
Alex Blakenburg
A

Forum statistics

Threads
1,224,827
Messages
6,181,194
Members
453,021
Latest member
pingpong7117

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