Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Cool Unanswered: Macro to Find a value and clear the row

    Hi,
    A workbook has 5 columns and 100 rows with data. In that 3 cells in Column A has text value entered as "total". Therefore I require a macro to search "total" (a text) in column A and clear the contents in the entire row. Can you please set this as loop so that it clears all the rows with text total in Column A

  2. #2
    Join Date
    Jun 2011
    Posts
    10
    kamal1jacq you could try something like this....

    Sub Macro2()
    '
    ' Macro2 Macro
    '

    '
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""total"",TRUE,"""")"
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B300"), Type:=xlFillDefault
    Calculate
    Columns("B:B").Select
    Selection.SpecialCells(xlCellTypeFormulas, 4).Select
    Selection.EntireRow.Delete
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    End Sub
    First 6 Lines: This will insert an extra column with a formula that looks for totals and puts a logical expression (TRUE) into the new column B for each cell in column A with "total" in it. You just need to make sure it goes down long enough for any possible length of table you may have, I have taken it down to 300 in this example; you could make this a couple of thousand if you wanted.

    The Selection.SpecialCells looks for the logical expressions in this column and the next row deletes any rows with logical expresions in them.

    The additional column is then deleted.


    I'm sure there is a loop procedure that might be more efficient but it has worked ok for me in the past both manually and as a macro.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •