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

earthworm

earthworm

Well-known Member
Joined
May 19, 2009
Messages
773
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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

W
  • Solved
Cell empty conundrum
Replies
4
Views
147
Westbury
W
A
  • Question Question
How to Unlink an Image/Picture in a spreadsheet to its original path
Replies
7
Views
309
Abegail0203
A
D
  • Solved
Could you please help me to write a macro for this situation?
Replies
6
Views
286
jolivanes
jolivanes
M
  • Question Question
VBA to return a list of ranges used by data validation
Replies
2
Views
239
Micron
M
H
  • Solved
Loop to test if cell not empty then move value to next column?
2
Replies
13
Views
559
jolivanes
jolivanes

Forum statistics

Threads
1,223,785
Messages
6,174,537
Members
452,571
Latest member
MarExcelTips

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