Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
range question | Excel Programming | |||
Range question | Excel Programming | |||
Used Range Question | Excel Programming | |||
Range.Formula and Range question using Excel Automation | Excel Programming |