Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

deleting rows

  Asked By: Matilda    Date: Mar 24    Category: MS Office    Views: 785
  

Below there is code which deletes empty rows based on CountA function of
Excel.
I wanted to change it to delete empty rows and those which have 2 used
cells in a row.
I just added
"or 2" to
"If Application.WorksheetFunction.CountA(Rows(r)) = 0 _".
Which makes
"If Application.WorksheetFunction.CountA(Rows(r)) = 0 or 2 _".
Then the code deletes all the rows with used cells.
Why the aforementioned alternative does not work?

Sub DeleteEmptyRows()
Dim LastRow As Long
Dim r As Long
LastRow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 _
Then Rows(r).Delete
Next r
End Sub

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Dannon Jones     Answered On: Mar 24

In VBA and most programming languages, you cannot just say "If x = 0 or
2", you must say "If x = 0 or x = 2", therefore your line should read:

If Application.WorksheetFunction.CountA(Rows(r)) = 0 Or _
Application.WorksheetFunction.CountA(Rows(r)) = 2 _

Or you could store the value in a variable first if you were really
concerned about performance:

Dim intNumCells As Integer
intNumCells = Application.WorksheetFunction.CountA(Rows(r))
If intNumCells = 0 Or IntNumCells = 2 ...

 
Answer #2    Answered By: Walborgd Fischer     Answered On: Mar 24


You can't just add "or 2" to an IF dtatment krzysztof.

You have to pu "or Application.WorksheetFunction.CountA(Rows(r)) = 2"

A neater way would be to use a variable and a select.

ilCount = Application.WorksheetFunction.CountA(Rows(r))
Select case ilCount
Case 0, 2
....
Case Else
End Select

 
Didn't find what you were looking for? Find more on deleting rows Or get search suggestion and latest updates.




Tagged:    

 
 
 
 

Related Post