Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / July 2008

Tip: Looking for answers? Try searching our database.

Complie error:  Wrong number of arguments or invalid property assi

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
MattyO - 31 Jul 2008 15:33 GMT
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 Thomlinson - 31 Jul 2008 16:35 GMT
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

Signature

HTH...

Jim Thomlinson

> I'm pretty new to this…
>
[quoted text clipped - 23 lines]
>
> Does this make sense?  Thanks in advance.
MattyO - 31 Jul 2008 17:57 GMT
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.

> 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
[quoted text clipped - 75 lines]
> >
> > Does this make sense?  Thanks in advance.
Jim Thomlinson - 31 Jul 2008 19:21 GMT
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...
Signature

HTH...

Jim Thomlinson

> Jim,
>
[quoted text clipped - 83 lines]
> > >
> > > Does this make sense?  Thanks in advance.
MattyO - 31 Jul 2008 21:38 GMT
Thanks!

> 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
[quoted text clipped - 88 lines]
> > > >
> > > > Does this make sense?  Thanks in advance.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.