Deleting Rows 1

[Wray69]

Hello All,

Im using the following code to delete rows from a spreadsheet;

Sub DeleteCells()

'Loop through cells A1:A10 and delete cells that contain an "x."
For Each c In Range("A1:A8341&quot
If c = "Client Total" Then c.EntireRow.Delete
Next

End Sub


Can anyone give me any ideas on how I can put INPUT boxs in so I can change the range and what I am searching for to delete?

Cheers,

Wray

 

[jrbarnett]

Hi,

You can use code along the following lines to produce basic facilities. Note that this code has very poor error checking.

Dim strRange As String
Dim strResult As String
Dim strFind As String
Dim intMin As Integer
Dim intMax As Integer

strResult = InputBox ("Enter Min value (RETURN to cancel)")
if strResult <> &quot;&quot; Then
  If IsNumeric (strResult) Then 
   intMin = Val (strResult)
  Else 
    intMin = -1
  End If
  strResult = InputBox (&quot;Enter Max value&quot;)
  intMax = Val (strResult)
  
  strFind = InputBox (&quot;Enter what to look for&quot;)
  strFind = UCase$ (strFind)

  For Each c In Range(&quot;A&quot; & Str (intMin) & &quot;:A&quot; & str(intMax))
      If UCase$ (c) = strFind Then c.EntireRow.Delete
  Next

John

 

[KenWright]

Hi Wray - If you are going to delete, then you need to do it from the ground up, ie from the last cell to the first. Doing it from top to bottom will mean you miss rows. Your code will work Ok if you only have odd rows dotted around, and never next to each other, but try putting Client Total in a bunch of contiguous cells and then running it. You will miss every other row.

Assume you have Client Total in rows 2 to 4. Your code will look at row 1 and see if it says Client Total - It doesn't and it moves on to row 2, it says 'yes it does contain it', and deletes the row and then moves on to row 3 - Problem is, what had been row 3 is now row 2 because you just deleted a row, so the next row the code ends up checking is actually what was row 4 (and is now the new row 3). Even though what was row 3 had Client Total in, it will now not get checked, and will be missed altogether.

The following will prompt you for a string, then start at the bottom and work up, so as to avoid missing any rows.

Sub DelRows()

Dim RowNdx As Long
Dim LastRow As Long
Dim ans As String

ans = InputBox("What string do you want rows to be deleted if it contains it?&quot

LastRow = ActiveSheet.Cells(Rows.Count, "A&quot.End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If StrComp(Cells(RowNdx, "A&quot, ans, vbBinaryCompare) = 0 Then
Cells(RowNdx, "A&quot.EntireRow.Delete
End If
Next RowNdx

End Sub

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[Wray69]

Hey Ken,

Thank You, that works beautifuly, Im slowly (very slowly) getting better at writing code, and I appreciate all the help you guys give me here. THanks again.

Cheers,

Wray

 

[KenWright]

That's Teamwork for you - and you are more than welcome.

Regards
Ken............

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------