How to skip macro if data in particular cell is empty or zero

earthworm

earthworm

Well-known Member
Joined
May 19, 2009
Messages
759
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I have written a macro to split a data into separate sheets . If the value in a particular cell example a3 has zero or empty then skip the marco and move to next , but if the value is cell a3 is not empty or value > 0 then run the macro . Please assist.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
I want to apply if the value in c3 is zero then skip macro and move to next
 
Upvote 0
Please post your code directly in the thread, select it and click the # to put code tags around it.
I have no intention of downloading a file (with the extra risks involved) just to read the code.
 
Upvote 0
Code: 
Sub Macro1()'


' ABC


   Range("B6").Select
    Sheets("Dump Data Here").Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$W$23916").AutoFilter Field:=4, Criteria1:= _
        "ABC"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveCell.Range("A1:W683").Select
    Selection.Columns.AutoFit
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Stop:=50000, Trend:=False
    Selection.End(xlDown).Select
    Range("B50001").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, -1).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\XYZ\Desktop\Test\ABC.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Sheets("Dump Data Here").Select
    Range("A1").Select
    Selection.AutoFilter
    Range("A1").Select
    Sheets("Summary").Select
    Range("A1").Select


' DEF


    Range("B6").Select
    Sheets("Dump Data Here").Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$W$23916").AutoFilter Field:=4, Criteria1:= _
        "DEF"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveCell.Range("A1:W683").Select
    Selection.Columns.AutoFit
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Stop:=50000, Trend:=False
    Selection.End(xlDown).Select
    Range("B50001").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, -1).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\XYZ\Desktop\Test\DEF.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Sheets("Dump Data Here").Select
    Range("A1").Select
    Selection.AutoFilter
    Range("A1").Select
    Sheets("Summary").Select
    Range("A1").Select


' GHI


    Range("B6").Select
    Sheets("Dump Data Here").Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$W$23916").AutoFilter Field:=4, Criteria1:= _
        "GHI"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveCell.Range("A1:W683").Select
    Selection.Columns.AutoFit
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Stop:=50000, Trend:=False
    Selection.End(xlDown).Select
    Range("B50001").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, -1).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\XYZ\Desktop\Test\GHI.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Sheets("Dump Data Here").Select
    Range("A1").Select
    Selection.AutoFilter
    Range("A1").Select
    Sheets("Summary").Select
    Range("A1").Select


' JKL


    Range("B6").Select
    Sheets("Dump Data Here").Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$W$23916").AutoFilter Field:=4, Criteria1:= _
        "JKL"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveCell.Range("A1:W683").Select
    Selection.Columns.AutoFit
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Stop:=50000, Trend:=False
    Selection.End(xlDown).Select
    Range("B50001").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, -1).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\XYZ\Desktop\Test\JKL.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Sheets("Dump Data Here").Select
    Range("A1").Select
    Selection.AutoFilter
    Range("A1").Select
    Sheets("Summary").Select
    Range("A1").Select
End Sub
 
Upvote 0
Hi

If you create a balise, you can programm a
IF something THEN

goto line

else

Do my code

end if

A line can be mark at the line as WHATEVER:

Code: 
Sub Macro1() 


'ABC

   Range("B6").Select

[ do your thing here]


' DEF
if range("C3")= 0 then 
goto JumpToNext_1
else
 [Do your things here]
end if


' GHI
JumpToNext_1:


if range("OTHER TEST REPALCE ME)=0 then

else
[do your thing here]

end if

' JKL
JumptoNext_2
 
Upvote 0
How about
Code: 
Sub Earthworm()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("ABC", "DEF", "GHI", "JKL")

   With Sheets("Dump Data Here")
      For i = 0 To UBound(Ary)
         If .AutoFilterMode Then .AutoFilterMode = False
         If Application.CountIf(Range("D:D"), Ary(i)) > 0 Then
            .Range("$A$1:$W$23916").AutoFilter 4, Ary(i)
            Workbooks.Add
            .AutoFilter.Range.Copy
            Range("A1").PasteSpecial xlPasteValues
            Range("A1").PasteSpecial xlPasteFormats
            Range("A1").CurrentRegion.Columns.AutoFit
            Range("A2").Value = 1
            If Range("A" & Rows.Count).End(xlUp).Row > 2 Then
               Range("A2").AutoFill Range("A2", Range("A" & Rows.Count).End(xlUp)), xlFillSeries
            End If
            Application.CutCopyMode = False
            Range("A1").Select
            ActiveWorkbook.SaveAs Filename:= _
               "C:\Users\XYZ\Desktop\Test\" & Ary(i) & ".xlsx", FileFormat:=51
            ActiveWorkbook.Close
         End If
      Next i
      .AutoFilterMode = False
   End With
End Sub
 
Upvote 0
Wanderer_geologist said:
Hi

If you create a balise, you can programm a
IF something THEN

goto line

else

Do my code

end if

A line can be mark at the line as WHATEVER:

Code: 
Sub Macro1()


'ABC

   Range("B6").Select

[ do your thing here]


' DEF
if range("C3")= 0 then
goto JumpToNext_1
else
[Do your things here]
end if


' GHI
JumpToNext_1:


if range("OTHER TEST REPALCE ME)=0 then

else
[do your thing here]

end if

' JKL
JumptoNext_2
Click to expand...

What if i want to start macro with a condition . will it be done this way

VBA Code: 
Sub Macro1()

if range("C2")= 0 then
Range("B6").Select
[ do your thing here]
else
if range("C3")= 0 then
Range("B7").Select
[ do your thing here]
end if
end Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA code: Copy Active Cell and Paste 2 cells/rows directly below
Replies
5
Views
405
mickyflash
M
M
  • Solved
Subtracting from an offset cell based on certain conditions
2
Replies
12
Views
167
Peter_SSs
Peter_SSs
H
  • Solved
Quickly select the 10th non-empty cell in column "A"
Replies
3
Views
170
Peter_SSs
Peter_SSs
Keebler
  • Solved
adding sort to a complicated array of rabbit holes and ...
2
Replies
12
Views
313
Keebler
Keebler
T
  • Solved
Check dynamic range for specific value
Replies
3
Views
153
mumps
mumps

Forum statistics

Threads
1,214,972
Messages
6,122,530
Members
449,088
Latest member
RandomExceller01

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