Range question w/VB
 

With the following macro (copied from this usenet, modified & working
thank you) is there a way the row number (in this example 771) updates
automatically when I manually add rows between 3 & 771.

******************
Dim rng As Range, i As Long
Dim lastrow As Long
With Worksheets("Sheet1")
Set rng = .Range(.Cells(3, 2), .Cells(771, 2).End(xlUp))
End With
With Worksheets("Sheet3")
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -1
If Application.CountIf(rng, .Cells(i, 1)) < 0 Then
.Rows(i).Delete
End If
Next i
End With
*********************

TIA

Ron

Range question w/VB
 

Hi Ron,

I would replace Set rng by the following :
Set rng=.Range("B3:B" & Range("A65536").End(xlUp).Row)

HTH
Cheers
Carim

Range question w/VB
 

Hi Carim,

Thanks for the reply...

I have populated cells in B2000:B3000 that when read give a false
result to the macro.

Cheers

Ron




On 25 Mar 2006 07:19:17 -0800, "Carim" wrote:

Hi Ron,

I would replace Set rng by the following :
Set rng=.Range("B3:B" & Range("A65536").End(xlUp).Row)

HTH
Cheers
Carim

Range question w/VB
 

What about Range("B1999").End(xlUp).Row) ?

Alternatively, put something in an empty column on line 771. Then use
the last populated row of that column.


--
Gordon Rainsford

London UK

Ron wrote:

Hi Carim,

Thanks for the reply...

I have populated cells in B2000:B3000 that when read give a false
result to the macro.

Cheers

Ron




On 25 Mar 2006 07:19:17 -0800, "Carim" wrote:

Hi Ron,

I would replace Set rng by the following :
Set rng=.Range("B3:B" & Range("A65536").End(xlUp).Row)

HTH
Cheers
Carim

Range question w/VB
 

Gordon,

Thanks for the suggestion but for this macro I moved the populated
B2000:B3000 cells to another worksheet and inserted the original line.

set rng = .Range(.cells(3,2),.cells(rows.count,2).End(xlup))

It works for me.

Regards

Ron

On Sat, 25 Mar 2006 16:52:43 +0000,
(Gordon Rainsford) wrote:

What about Range("B1999").End(xlUp).Row) ?

Alternatively, put something in an empty column on line 771. Then use
the last populated row of that column.

Hi Carim,

Thanks for the reply...

I have populated cells in B2000:B3000 that when read give a false
result to the macro.

Cheers

Ron




On 25 Mar 2006 07:19:17 -0800, "Carim" wrote:

Hi Ron,

I would replace Set rng by the following :
Set rng=.Range("B3:B" & Range("A65536").End(xlUp).Row)

HTH
Cheers
Carim

With the following macro (copied from this usenet, modified & working
thank you) is there a way the row number (in this example 771) updates
automatically when I manually add rows between 3 & 771.

******************
Dim rng As Range, i As Long
Dim lastrow As Long
With Worksheets("Sheet1")
Set rng = .Range(.Cells(3, 2), .Cells(771, 2).End(xlUp))
End With
With Worksheets("Sheet3")
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -1
If Application.CountIf(rng, .Cells(i, 1)) < 0 Then
.Rows(i).Delete
End If
Next i
End With
*********************

TIA

Ron