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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
487
Peter_SSs
Peter_SSs
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
264
rlv01
R
E
  • Question Question
Cleaning up some "Select" code
2
Replies
17
Views
239
EssKayKay
E
Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
444
Sean15
Sean15
S
  • Question Question
Macro Formatting to all WS
Replies
4
Views
317
MARK858
MARK858

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

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