Loop to delete rows if it finds particular text

[fari1]

I want the vba code, that loops through each row from A1:J20 and if it finds $ sign in any first row(means the first occurance of $) i want it to delete all the rows above it.

 

[Rick Rothstein]

Give this macro a try and see if it does what you want...

Sub ClearAboveDollarSign()
  Dim DollarSign As Range
  Set DollarSign = Range("A1:J20").Find("$", Range("J20"), xlValues, xlPart, xlByRows, xlNext)
  If Not DollarSign Is Nothing Then
    If DollarSign.Row > 1 Then Rows("1:" & DollarSign.Row - 1).ClearContents
  End If
End Sub

 

[fari1]

brilliant, its awsum thanks alot, but i also want to offset this data to the first row, move the data starting from $ sign to the first row of the sheet

 

[Rick Rothstein]

brilliant, its awsum thanks alot, but i also want to offset this data to the first row, move the data starting from $ sign to the first row of the sheet

In that case, change the ClearContents to Delete...

Sub ClearAboveDollarSign()
  Dim DollarSign As Range
  Set DollarSign = Range("A1:J20").Find("$", Range("J20"), xlValues, xlPart, xlByRows, xlNext)
  If Not DollarSign Is Nothing Then
    If DollarSign.Row > 1 Then Rows("1:" & DollarSign.Row - 1).Delete
  End If
End Sub

 

[fari1]

yea its perfect, thanks alot, can this code include one more function? i want the code to look for $ sign from column A:I, i want to delete the columns that has $ sign

 

[Rick Rothstein]

Sorry about the delay... I had gone to sleep for the night. Give this modification to my macro a try (it will handle your initial request in addition to your latest one)...

Sub ClearAboveDollarSign()
  Dim DollarSign As Range, Cell As Range, FirstAddress As String
  Set DollarSign = Range("A1:J20").Find("$", Range("J20"), xlValues, xlPart, xlByRows, xlNext)
  If Not DollarSign Is Nothing Then
    If DollarSign.Row > 1 Then Rows("1:" & DollarSign.Row - 1).Delete
  End If
  With Columns("A:I")
    Set Cell = .Find("$", Range("I" & Rows.Count), xlValues, xlPart, xlByRows, xlNext)
    If Not Cell Is Nothing Then
      Do
        Cell.EntireColumn.Delete
        Set Cell = .Find("$", Range("I" & Rows.Count), xlValues, xlPart, xlByRows, xlNext)
      Loop While Not Cell Is Nothing
    End If
  End With
End Sub

 

[fari1]

No worries, thanks for your reply.
someone from this forum already replied to it and its working, i asked another question, will you please be so considerate to resolve it, i want to move data from one column to another, my sheet has two column of just closing brackets ")", i want the vba code to look through columns A:I and look for the closing brackets, if it finds them in a column then move them to the left column which already contain numeric data. e.g (24 is in one column and closing bracket is in other. i want to combine them in one column.
will be highly thankful if you resolve this. my thread is located at
http://www.mrexcel.com/forum/showthread.php?p=2792674

 

[Rick Rothstein]

*** CORRECTION TO MY LAST MESSAGE ****

I would have edited my last message but the 10-minute time limit had expired. Anyway, use this code instead of what I posted last (there is a problem with that code)...

Sub ClearAboveDollarSign()
  Dim DollarSign As Range, Cell As Range, FirstAddress As String
  Set DollarSign = Range("A1:J20").Find("$", Range("J20"), xlValues, xlPart, xlByRows, xlNext)
  If Not DollarSign Is Nothing Then
    If DollarSign.Row > 1 Then Rows("1:" & DollarSign.Row - 1).Delete
  End If
  With Columns("A:I")
    Set Cell = .Find("$", Range("I" & Rows.Count), xlValues, xlPart, xlByRows, xlNext)
    If Not Cell Is Nothing Then
      Do
        Cell.Value = "#N/A"
        Set Cell = .Find("$", Range("I" & Rows.Count), xlValues, xlPart, xlByRows, xlNext)
      Loop While Not Cell Is Nothing
    End If
    .SpecialCells(xlCellTypeConstants, xlErrors).EntireColumn.Delete
  End With
End Sub

FOLLOW UP NOTE: You said in a message before this one that someone from the forum had resolve your latest question... where? I do not see such a message in this thread... all I see are messages between you and me.

 

[fari1]

Thanks, its working, plz reply to my other code, i desparately need the solution of that

 

[Rick Rothstein]

Thanks, its working, plz reply to my other code, i desparately need the solution of that

I just did.

I'm still curious, though, where the other message you referred to is. Are you saying you see a message in this thread from someone other than you are me? If so, please post that person's sign-on name. Thanks.