Jim,
That's close. I want it to find cells that contain zero "0" starting from
Row 10 (for which ever column I choose).
I can see in your code I just need to change Value = "" to Value = "0", but
what other tweaks to do I need to make for this to work.
"Jim Thomlinson" wrote:
> Your code does not do what you think it does. It traverses through all cell
> P10 thorugh W?? deleteing the entire row if a blank is found in any cell in
> that range. That being said even then the results are unpredictable because
> you are deleting rows in the range you are trying to traverse through... Last
> but not least your variable intNumRows is probably declared as an integer
> somewhere which could be a problem since there are 65k rows in a spreadsheet
> but integer only allows numbers up to 32k. Generally speaking the code is not
> going to work... If I understand you correctly you want to delete a row if it
> contains a blank in P and W and ... This should work for you...
>
> Public Sub DeleteStuff()
> Dim lngLastRow As Range
> Dim lng As Long
>
> Set lngLastRow = LastCell(ActiveSheet).Row
> For lng = lngLastRow To 2 Step -1
> If Cells(lng, "P").Value = "" And Cells(lng, "W").Value = "" And _
> Cells(lng, "Z").Value = "" And Cells(lng, "AE").Value = "" And _
> Cells(lng, "AR").Value = "" Then
> Rows(lng).EntireRow.Delete
> End If
> Next lng
> End Sub
>
> Public Function LastCell(Optional ByVal wks As Worksheet) As Range
> Dim lngLastRow As Long
> Dim intLastColumn As Integer
>
> If wks Is Nothing Then Set wks = ActiveSheet
> On Error Resume Next
> lngLastRow = wks.Cells.Find(What:="*", _
> After:=wks.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Row
> intLastColumn = wks.Cells.Find(What:="*", _
> After:=wks.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Column
> On Error GoTo 0
> If lngLastRow = 0 Then
> lngLastRow = 1
> intLastColumn = 1
> End If
> Set LastCell = wks.Cells(lngLastRow, intLastColumn)
> End Function
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "MattyO" wrote:
>
> > I'm pretty new to this…
> >
> > It's a 2 part question.
> >
> > 1) I have this procedure below that works fine with 2 columns. When I add
> > a third column, say column "AE" I get the compile error in the subject line
> > above, why? I wouldn't think it would matter the number of columns I
> > provide here.
> >
> > Private Sub DeleteBlankRows()
> >
> > Dim c As Range
> >
> > For Each c In Range("P10:P" & intNumRows, "W10:W" & intNumRows)
> > If c.Value = "0" Or c.Value = Null Then
> > c.EntireRow.Delete xlUp
> > End If
> > Next c
> > End Sub
> >
> > 2) If I understand this procedure correctly, the way it sits right now, it
> > will only delete a row if it is zero or null for columns “P” AND “W”
> > (*starting at row 10 and going to wherever the last row maybe). If not, that
> > is where I want this to go. Delete a row if it's zero or null for columns
> > P,W, Z, AE, AR, etc.
> >
> > Does this make sense? Thanks in advance.
> >
|
Similar Threads
