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.

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.

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.

Mine works to row 2 so change the 2 to a 10. Because it is deleting it moves
from the bottom to the top. Change the "" to 0 and not "0" assuming the the
values are number zero and not text 0. Other than that you cna change the And
conditions to Or so that if it is 0 in P or Z or...
--
HTH...

Jim Thomlinson


"MattyO" wrote:

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.

Thanks!

"Jim Thomlinson" wrote:

Mine works to row 2 so change the 2 to a 10. Because it is deleting it moves
from the bottom to the top. Change the "" to 0 and not "0" assuming the the
values are number zero and not text 0. Other than that you cna change the And
conditions to Or so that if it is 0 in P or Z or...
--
HTH...

Jim Thomlinson


"MattyO" wrote:

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.