Count number of Rows in array

[CTKC]

Hello All,

Background info: I am in finance with no coding expierence and I am trying to learn VBA (teaching myself). I started to learn/read about VBA about 2 weeks ago so i am very new. I am working toward learning the language as quickly as possible.

The question is that I have a piece of code (below) that works fine but runs a little slow. The number of rows vary from month to month when i pull this report. I have an array named "CAM" and would like to count the number of rows in this array and instert it where the 10000 is. So we would not go through rows with no data in it and it should process faster (i believe).


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = total To 2 Step -1
Select Case Range("F" & i).Value
Case "MOORESTOWN", "SYRACUSE", "Manassas", "Baltimore"
'do nothing
Case Else
Rows(i).EntireRow.Delete
End Select
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub



I thought this would work but it does not...

dim total as integer
dim count as integer

count = 2

Do Until Cells(count, 2).Value <> ""
If Cells(count, 2).Value <> "" Then
'do nothing
End If

count = count + 1
Loop
total = count -------then plug total where 10000 is in the above code. I thought this owuld just count the number of cells in the array and then i would apply the count to total. but i must be missing something.


Thanks for the help in advance!

 

[SkipVought]

Hi,

There are ARRAYS and then there are RANGES -- two different animals, with some similarities.

So are we talking arrays, a purely VB thing, where have nothing, necessarily to do with ranges, or a range on a sheet?

What is special about 10000?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[xlbo]

Think you are talking about ranges rather than arrays

2 things

1st - best bet is not to loop to find your last data element. There are 2 FAQs in this forum that deal with finding the last element in a column

2nd - I wouldn;t loop to perform the deletions. It will actually be quicker to get the code to:
write a simple if formula to an empty column to the right of your data
filter on that column
delete all rows
remove autofilter

try doing it manually and record yourself doing it. Post the code back and we can help modify it so it can be run repeatedly

as an example, I would use an IF statement like:

=if(OR(F2="MOORESTOWN",F2="SYRACUSE",F2="MAnassas",F2="Baltimore"),"KEEP","DELETE")

This can be written to all your cells at 1 go so no need for loops. Where lRow is the last row of data:

Range("M2:M" & lRow).formula = "=if(OR(F2="MOORESTOWN",F2="SYRACUSE",F2="MAnassas",F2="Baltimore"),"KEEP","DELETE")"

then filter for that column = "DELETE" and delete all rows

et voila

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[CTKC]

Skip,

Sorry i should have said a Range, you are correct. There is nothing special about 10000. I just plugged 10000 becuase i knew the data would never go above 10000 rows (so far it hasn't anyway). But i would like the code to run a little faster as the code goes from row 10000 to row 2. Some of those rows contain no data so why even evaluate them & deleting the row - taking up time, right? I would like to find out how many rows are in the named range CAM and apply that number where 10000 is. The code i posted previously was tested and did not work the way i wanted.

the code i have right now is:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = 10000 To 2 Step -1
Select Case Range("F" & i).Value
Case 'criteria
'do nothing
Case Else
Rows(i).EntireRow.Delete
End Select
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[SkipVought]

'reference your FIRST CELL with data
'this assumes a contiguous range in this column to the LAST CELL
   dim rng as range
'this sets a range from A1 to the last cell containing contiguous data in column A
   set rng = range(cells(1,1), cells(1,1).end(xldown))
'or
   lLastRow = range(cells(1,1), cells(1,1).end(xldown)).row

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[CTKC]

xlbo,

I recorded the formula...

Sub newtest()
Dim strfrm

strfrm = "=IF(OR(RC[-1]=""MOORESTOWN"",RC[-1]=""SYRACUSE"",RC[-1]=""MAnassas"",RC[-1]=""Baltimore""),""KEEP"",""DELETE"")"

With Range("keepordelete")
.Formula = strfrm
.Copy
.PasteSpecial xlValues
End With
Application.CutCopyMode = False
' below is recorded
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:="=DELETE", Operator:=xlAnd
Rows("124:3680").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.AutoFilter

Range("A1").Select



Only question is that how do i select the rows and delete them because they will not always be 124:3000....



Skip,


thank you for your input. I will test this and see what happens. I appreciate it tremendously!

 

[SkipVought]

"Only question is that how do i select the rows and delete them because they will not always be 124:3000...."

Cells(1,1).AutoFilter
Cells(1,1).AutoFilter Field:=7, Criteria1:="=DELETE
Cells(1,1).currentregion.Delete Shift:=xlUp
Cells(1,1).AutoFilter
Cells(1,1).Select

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[CTKC]

Cells(1,1).currentregion.Delete Shift:=xlUp

i stepped through the code and each time it executes this line it deleted my headings in row 1...i changed it to cells(2,1) but it does the same thing....

 

[SkipVought]

sorry, I wasn't thinking...

range(cells(2,1), cells(cells(1,1).currentregion.rows.count,1)).delete shift:=xlup

hint: before doing any add/change/delete in a range, test with a SELECT

range(cells(2,1), cells(cells(1,1).currentregion.rows.count,1)).select

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[CTKC]

Skip,

thanks again bud!

Question from the code you orginally gave me...I keep getting a mismatch error when i try to run it...heres the code

Dim lLastRow As Range

Set lLastRow = Range(Cells(2, 2), Cells(6, 6).End(xlDown)).Row
' ^ this should go to the last row in column F , right?

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = lLastRow To 2 Step -1
Select Case Range("F" & i).Value
Case "MOORESTOWN", "SYRACUSE", "Manassas", "Baltimore"
'do nothing
Case Else
Rows(i).EntireRow.Delete
End Select
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[PHV]

Simply remove the Set keyword as you aren't setting an object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Set lLastRow = Range(Cells(2, 2), Cells(6, 6).End(xlDown)).Row 
' ^ this should go to the last row in column F , right?

cells(2,2) is row 2 column B. Does column B have CONTIGUOUS data from row 2 thru the last row of data? If the answer is "yes" then there is no need to use column F. All you need is...

Set lLastRow = Range(Cells(2, 2), Cells(2,2).End(xlDown)).Row

to get the last row.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ck1999]

Skip,

Because of possibly not having the CONTIGUOUS data should she use as

cells(60000,6).end(xlup).row

long as if the row has data that column F has data


ck1999

 

[SkipVought]

This is why you have to know and understand your data. I never deal with data that is not a real table, and data that has contiguous data in at least one column, almost always in the left-most column.

Even if you use cells(65536, c).end(xlup), you MUST know that column c will have data in the last row. And it REALLY goes beyond that. Because data can be SORTED, c CANNOT or ought not contain ANY empty cells.

But if you do have some squirly situation, I'd use something like this...

    Dim r As Range, lLastRow As Long
    Set r = Cells.Find("*", [IV65536])
    lLastRow = Cells.FindPrevious(r).Row
    MsgBox r.Row

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}