Excel Task ideas

Jemma Atkinson

Jemma Atkinson

Well-known Member
Joined
Jul 7, 2008
Messages
509
Hi, i have a workbook which has 3 worksheets("Cash", "Securities", Physical Securities")

This workbook which contains outstanding reconcilliation breaks gets sent to the client each month.

In Sheet "Cash" Col U is Status, which is either blank or has the string "CLEARED" for each row of data

In Sheet "Securities" it will be Col S and sheet Physical Securities it will Col R.

Question i only want to show the client the data which is not CLEARED. What is the best approach in doing this?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
You could use advanced filter to extract the CLEARED items from each worksheet to a new worksheet.

Then send just that worksheet to the client.

Or use it to extract the CLEARED items for each worksheet to a new worksheet and send all 3 worksheets.
 
Upvote 0
Marcelo, if i did use this then would i need to copy this code 3 times for each worksheet? How can i use only one code but delete in 3 worksheets in different columns

Code: 
Sub Delete_with_Autofilter()
    Dim DeleteValue As String
    Dim rng As Range
    Dim calcmode As Long

    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'Fill in the value that you want to delete
    'Tip: use DeleteValue = "<>CLEARED" to delete rows without ron
    DeleteValue = "CLEARED"

    'Sheet with the data, you can also use Sheets("MySheet")
    With ActiveSheet
        'Firstly, remove the AutoFilter
        .AutoFilterMode = False
        'Apply the filter
        .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue

        With .AutoFilter.Range
            On Error Resume Next
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                      .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng Is Nothing Then rng.EntireRow.Delete
        End With
        'Remove the AutoFilter
        .AutoFilterMode = False
    End With

    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With

End Sub
 
Upvote 0
Jemma Atkinson said:
Marcelo, if i did use this then would i need to copy this code 3 times for each worksheet? How can i use only one code but delete in 3 worksheets in different columns
Click to expand...

Jemma,

Maybe something like this
(please, try it on a test-workbook. I did only one simple test. Needs to be fully tested)

Small changes in Ron de Bruin macro, including parameters.

Code: 
Sub Delete_with_Autofilter(wk As String, colHeader As String)
    Dim DeleteValue As String
    Dim rng As Range, lastRow As Long
    Dim calcmode As Long
    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    'Fill in the value that you want to delete
    'Tip: use DeleteValue = "<>CLEARED" to delete rows without ron
    DeleteValue = "CLEARED"
    'Sheet with the data, you can also use Sheets("MySheet")
    With Sheets(wk)
        'Firstly, remove the AutoFilter
        .AutoFilterMode = False
        'Get the lastRow in column col
        lastRow = .Cells(.Rows.Count, colHeader).End(xlUp).Row
        'Set the range
        Set rng = Range(.Cells(1, colHeader), .Cells(lastRow, colHeader))
        'Apply the filter
        rng.AutoFilter Field:=1, Criteria1:=DeleteValue
        With .AutoFilter.Range
            On Error Resume Next
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                      .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng Is Nothing Then rng.EntireRow.Delete
        End With
        'Remove the AutoFilter
        .AutoFilterMode = False
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With
End Sub

Then create the macro below that calls the Ron's macro for each worksheet passing parameters

Code: 
Sub callDelRows()
    'In Sheet "Cash" Col U is Status, which is either blank or has the string "CLEARED" for each row of data
    'In Sheet "Securities" it will be Col S and sheet Physical Securities it will Col R
    Call Delete_with_Autofilter("Cash", "U")
    Call Delete_with_Autofilter("Securities", "S")
    Call Delete_with_Autofilter("Physical Securities", "R")
End Sub

Important: you must have Headers (Status) on these columns, as the auto-filter takes the first row as a header.

HTH

M.
 
Upvote 0
Or try this (I used Advanced Filter):

IMPORTANT: do some tests with a copy of you workbook first.

My examples sheets:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>Q</TH><TH>R</TH><TH>S</TH><TH>T</TH><TH>U</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Col01</TD><TD>Col02</TD><TD>Col17</TD><TD>Col18</TD><TD>Col19</TD><TD>Col20</TD><TD style="BACKGROUND-COLOR: #ffff00">Status</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Data700</TD><TD>Data985</TD><TD>Data423</TD><TD>Data325</TD><TD>Data204</TD><TD>Data958</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Data733</TD><TD>Data915</TD><TD>Data262</TD><TD>Data607</TD><TD>Data880</TD><TD>Data830</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Data390</TD><TD>Data349</TD><TD>Data902</TD><TD>Data706</TD><TD>Data672</TD><TD>Data290</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Data533</TD><TD>Data691</TD><TD>Data615</TD><TD>Data782</TD><TD>Data812</TD><TD>Data196</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Data997</TD><TD>Data554</TD><TD>Data844</TD><TD>Data854</TD><TD>Data821</TD><TD>Data632</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Data335</TD><TD>Data801</TD><TD>Data852</TD><TD>Data202</TD><TD>Data453</TD><TD>Data510</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Data603</TD><TD>Data229</TD><TD>Data206</TD><TD>Data121</TD><TD>Data618</TD><TD>Data700</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Data495</TD><TD>Data502</TD><TD>Data247</TD><TD>Data586</TD><TD>Data706</TD><TD>Data266</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Data249</TD><TD>Data766</TD><TD>Data575</TD><TD>Data782</TD><TD>Data271</TD><TD>Data584</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>Data499</TD><TD>Data405</TD><TD>Data162</TD><TD>Data184</TD><TD>Data203</TD><TD>Data404</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Data885</TD><TD>Data858</TD><TD>Data850</TD><TD>Data515</TD><TD>Data841</TD><TD>Data413</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>Data768</TD><TD>Data240</TD><TD>Data889</TD><TD>Data204</TD><TD>Data882</TD><TD>Data558</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>Data120</TD><TD>Data536</TD><TD>Data807</TD><TD>Data523</TD><TD>Data953</TD><TD>Data212</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>Data490</TD><TD>Data777</TD><TD>Data172</TD><TD>Data476</TD><TD>Data784</TD><TD>Data991</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD>Data209</TD><TD>Data677</TD><TD>Data653</TD><TD>Data210</TD><TD>Data418</TD><TD>Data765</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD>Data408</TD><TD>Data695</TD><TD>Data716</TD><TD>Data668</TD><TD>Data920</TD><TD>Data377</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>Data213</TD><TD>Data130</TD><TD>Data964</TD><TD>Data807</TD><TD>Data171</TD><TD>Data748</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD>Data976</TD><TD>Data459</TD><TD>Data489</TD><TD>Data576</TD><TD>Data127</TD><TD>Data638</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD>Data305</TD><TD>Data296</TD><TD>Data315</TD><TD>Data862</TD><TD>Data813</TD><TD>Data325</TD><TD></TD></TR></TBODY></TABLE>Cash



<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>Q</TH><TH>R</TH><TH>S</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Col01</TD><TD>Col02</TD><TD>Col17</TD><TD>Col18</TD><TD style="BACKGROUND-COLOR: #ffff00">Status</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Data248</TD><TD>Data578</TD><TD>Data624</TD><TD>Data216</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Data523</TD><TD>Data785</TD><TD>Data644</TD><TD>Data354</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Data561</TD><TD>Data756</TD><TD>Data277</TD><TD>Data356</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Data906</TD><TD>Data139</TD><TD>Data870</TD><TD>Data946</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Data710</TD><TD>Data923</TD><TD>Data198</TD><TD>Data582</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Data828</TD><TD>Data492</TD><TD>Data262</TD><TD>Data440</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Data642</TD><TD>Data206</TD><TD>Data176</TD><TD>Data113</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Data826</TD><TD>Data480</TD><TD>Data261</TD><TD>Data277</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Data723</TD><TD>Data677</TD><TD>Data292</TD><TD>Data506</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>Data426</TD><TD>Data900</TD><TD>Data258</TD><TD>Data819</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Data729</TD><TD>Data246</TD><TD>Data161</TD><TD>Data394</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>Data701</TD><TD>Data382</TD><TD>Data832</TD><TD>Data667</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>Data764</TD><TD>Data348</TD><TD>Data164</TD><TD>Data252</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>Data419</TD><TD>Data244</TD><TD>Data536</TD><TD>Data842</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD>Data479</TD><TD>Data704</TD><TD>Data523</TD><TD>Data441</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD>Data361</TD><TD>Data448</TD><TD>Data172</TD><TD>Data245</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>Data429</TD><TD>Data871</TD><TD>Data189</TD><TD>Data656</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD>Data532</TD><TD>Data417</TD><TD>Data519</TD><TD>Data297</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD>Data514</TD><TD>Data702</TD><TD>Data267</TD><TD>Data659</TD><TD></TD></TR></TBODY></TABLE>
Securities



<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>Q</TH><TH>R</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Col01</TD><TD>Col02</TD><TD>Col03</TD><TD>Col17</TD><TD style="BACKGROUND-COLOR: #ffff00">Status</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Data194</TD><TD>Data472</TD><TD>Data255</TD><TD>Data505</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Data392</TD><TD>Data696</TD><TD>Data279</TD><TD>Data288</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Data242</TD><TD>Data368</TD><TD>Data570</TD><TD>Data702</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Data787</TD><TD>Data505</TD><TD>Data839</TD><TD>Data336</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Data465</TD><TD>Data699</TD><TD>Data378</TD><TD>Data507</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Data315</TD><TD>Data649</TD><TD>Data358</TD><TD>Data190</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Data349</TD><TD>Data555</TD><TD>Data250</TD><TD>Data517</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Data547</TD><TD>Data976</TD><TD>Data575</TD><TD>Data275</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Data901</TD><TD>Data715</TD><TD>Data542</TD><TD>Data920</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>Data141</TD><TD>Data719</TD><TD>Data232</TD><TD>Data341</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Data910</TD><TD>Data374</TD><TD>Data157</TD><TD>Data112</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>Data968</TD><TD>Data144</TD><TD>Data429</TD><TD>Data835</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>Data689</TD><TD>Data177</TD><TD>Data336</TD><TD>Data302</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>Data695</TD><TD>Data679</TD><TD>Data739</TD><TD>Data825</TD><TD>CLEARED</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD>Data228</TD><TD>Data615</TD><TD>Data352</TD><TD>Data260</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD>Data874</TD><TD>Data924</TD><TD>Data450</TD><TD>Data430</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>Data500</TD><TD>Data179</TD><TD>Data592</TD><TD>Data397</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD>Data128</TD><TD>Data436</TD><TD>Data375</TD><TD>Data728</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD>Data548</TD><TD>Data101</TD><TD>Data779</TD><TD>Data932</TD><TD>CLEARED</TD></TR></TBODY></TABLE>Physical Securities



Result - only for sheet Data1

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>Q</TH><TH>R</TH><TH>S</TH><TH>T</TH><TH>U</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Col01</TD><TD>Col02</TD><TD>Col03</TD><TD>Col17</TD><TD>Col18</TD><TD>Col19</TD><TD>Col20</TD><TD style="BACKGROUND-COLOR: #ffff00">Status</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Data733</TD><TD>Data915</TD><TD>Data876</TD><TD>Data262</TD><TD>Data607</TD><TD>Data880</TD><TD>Data830</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Data533</TD><TD>Data691</TD><TD>Data674</TD><TD>Data615</TD><TD>Data782</TD><TD>Data812</TD><TD>Data196</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Data495</TD><TD>Data502</TD><TD>Data196</TD><TD>Data247</TD><TD>Data586</TD><TD>Data706</TD><TD>Data266</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Data249</TD><TD>Data766</TD><TD>Data820</TD><TD>Data575</TD><TD>Data782</TD><TD>Data271</TD><TD>Data584</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Data499</TD><TD>Data405</TD><TD>Data731</TD><TD>Data162</TD><TD>Data184</TD><TD>Data203</TD><TD>Data404</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD>Data768</TD><TD>Data240</TD><TD>Data609</TD><TD>Data889</TD><TD>Data204</TD><TD>Data882</TD><TD>Data558</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD>Data120</TD><TD>Data536</TD><TD>Data308</TD><TD>Data807</TD><TD>Data523</TD><TD>Data953</TD><TD>Data212</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Data976</TD><TD>Data459</TD><TD>Data682</TD><TD>Data489</TD><TD>Data576</TD><TD>Data127</TD><TD>Data638</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>Data305</TD><TD>Data296</TD><TD>Data197</TD><TD>Data315</TD><TD>Data862</TD><TD>Data813</TD><TD>Data325</TD><TD></TD></TR></TBODY></TABLE>Data1




My code and macro comments:

Code: 
Sub Create3Sheets()
    Dim mySheet As Worksheet, i As Integer
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
 
    For i = 1 To 3
        On Error Resume Next
        Sheets("Data" & i).Delete
        On Error GoTo 0
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Data" & i
        Set mySheet = Sheets(Choose(i, "Cash", "Securities", "Physical Securities"))
        LastRow = mySheet.Cells(Rows.Count, 1).End(xlUp).Row
        Lastcol = mySheet.Cells(11, Columns.Count).End(xlToLeft).Column
        Cells(1, Lastcol + 2).Value = "Status"
        Cells(2, Lastcol + 2).Formula = "=""<>CLEARED"""
        mySheet.Range("A1").CurrentRegion.AdvancedFilter _
            Action:=xlFilterCopy, _
            CriteriaRange:=Range(Cells(1, Lastcol + 2), Cells(2, Lastcol + 2)), _
            CopyToRange:=Range("A1")
        Range(Cells(1, Lastcol + 2), Cells(2, Lastcol + 2)).Clear
    Next i
 
    Sheets(Array("Data1", "Data2", "Data3")).Move
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\DATANOTCLEARED"
    ActiveWorkbook.Close
 
    Set mySheet = Nothing
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Code: 
'
'IMPORTANT: all the sheets Cash, Securities and
'Physical Securities need to have a field Status
'
'Define the macro variables
'Disable Screen Update
'Turns off alert messages so Excel doesn't ask
'whether you really want to delete a sheet
'
'Navigate for the sheets Data1, Data2 and Data3
'Ignore any run-time errors and simply continue
'with the next statement (in the case of the
'sheet that is having deleted not exist)
'
'
'Create the sheets Data1,Data2 and Data3 in
'the master workbook
'
'Enable any run-time errors
'
'Reference the sheets Cash or Securities,or Physical Securities
'Determine the last row and last column with data
'of the sheet Cash or Securities or Physical Securities
'Select the sheet Data1 or Data2 or Data3
'Define the criteria area (criteria -> <>CREARED) for the filter
'Filter the data of any sheeet Cash, Securities,
'Physical Securities, with the criteria not CREARED
'in the field Status, to the sheet Data1, Data2 and Data3
'
'Create a new workbook as move the sheets Data1, Data2
'and Data3 to it, save the new workbook with the name
'DATANOTCLEARED in this master workbook path and closed it
'
'Free the memory used for the object variable
'Turns on alert messages
'Enable Screen Update
 
Upvote 0
I noticed when there is no "CLEARED" string then the header row moves from Row 5 to row 4, why is this?





Marcelo Branco said:
Jemma,

Maybe something like this
(please, try it on a test-workbook. I did only one simple test. Needs to be fully tested)

Small changes in Ron de Bruin macro, including parameters.

Code: 
Sub Delete_with_Autofilter(wk As String, colHeader As String)
    Dim DeleteValue As String
    Dim rng As Range, lastRow As Long
    Dim calcmode As Long
    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    'Fill in the value that you want to delete
    'Tip: use DeleteValue = "<>CLEARED" to delete rows without ron
    DeleteValue = "CLEARED"
    'Sheet with the data, you can also use Sheets("MySheet")
    With Sheets(wk)
        'Firstly, remove the AutoFilter
        .AutoFilterMode = False
        'Get the lastRow in column col
        lastRow = .Cells(.Rows.Count, colHeader).End(xlUp).Row
        'Set the range
        Set rng = Range(.Cells(1, colHeader), .Cells(lastRow, colHeader))
        'Apply the filter
        rng.AutoFilter Field:=1, Criteria1:=DeleteValue
        With .AutoFilter.Range
            On Error Resume Next
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                      .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng Is Nothing Then rng.EntireRow.Delete
        End With
        'Remove the AutoFilter
        .AutoFilterMode = False
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With
End Sub
Then create the macro below that calls the Ron's macro for each worksheet passing parameters

Code: 
Sub callDelRows()
    'In Sheet "Cash" Col U is Status, which is either blank or has the string "CLEARED" for each row of data
    'In Sheet "Securities" it will be Col S and sheet Physical Securities it will Col R
    Call Delete_with_Autofilter("Cash", "U")
    Call Delete_with_Autofilter("Securities", "S")
    Call Delete_with_Autofilter("Physical Securities", "R")
End Sub
Important: you must have Headers (Status) on these columns, as the auto-filter takes the first row as a header.

HTH

M.
Click to expand...
 
Upvote 0
Jemma Atkinson said:
I noticed when there is no "CLEARED" string then the header row moves from Row 5 to row 4, why is this?
Click to expand...

Hi Jemma,

Yes, when there is no CLEARED there is a problem.

To fix it, after
Set rng = Range(.Cells(1, colHeader), .Cells(lastRow, colHeader))

include this code-line
If Application.CountIf(rng, DeleteValue) = 0 Then Exit Sub

HTH

M.
 
Upvote 0
This will use advanced filter to copy the non-cleared rows on each worksheet to a new workbook, with a worksheet for each original worksheet.

It then saves the new workbook.
Code: 
Option Explicit
 
Sub GetNonCleared()
Dim wbNew As Workbook
Dim wsTemp As Worksheet
Dim wsData As Worksheet
Dim wsNew As Worksheet
Dim rngCrit As Range
Dim rngData As Range
 
    Set wbNew = Workbooks.Add(xlWBATWorksheet)
    
    
    Set wsTemp = ThisWorkbook.Worksheets.Add
    
    wsTemp.Range("A1") = "Status"
    
    wsTemp.Range("A2") = "=""<>""&""CLEARED"""
    
    Set rngCrit = wsTemp.Range("A1:A2")
    
    For Each wsData In ThisWorkbook.Worksheets(Array("Cash", "Securities", "Physical Securities"))
    
        Set rngData = wsData.Range("A1").CurrentRegion
        
        Set wsNew = wbNew.Worksheets(wbNew.Worksheets.Count)
        
        wsNew.Name = wsData.Name
        
        rngData.AdvancedFilter xlFilterCopy, rngCrit, wsNew.Range("A1"), True
        
        Set wsNew = wbNew.Worksheets.Add(After:=wsNew)
        
    Next wsData
    
    Application.DisplayAlerts = False

    wsTemp.Delete

    Application.DisplayAlerts = True
    
    wbNew.SaveAs ThisWorkbook.Path & "\Reconciliation" & Format(Date, "ddmmmyy")
    
End Sub
 
Upvote 0
Jemma,

I have to say: i would adopt Norie's approach - create a new worbook with only the relevant data to your clients.

Its a much better practice and safer than delete rows (or anything) of your original workbook.

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Excel Time Keeping "Application"
Replies
0
Views
556
Mrock1
M
I
  • Solved
Textbox on userform not updating when navigating through records
2
Replies
10
Views
521
ipbr21054
I
R
  • Question Question
Checking Large Data table for text matched in a row to be selected by an id
Replies
1
Views
332
RobP
R
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
430
ChrisMac1
ChrisMac1
T
  • Solved
How can I filter duplicates from a worksheet column out of a data validation list drop-down box?
Replies
5
Views
224
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,599
Messages
6,179,828
Members
452,946
Latest member
JoseDavid

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