Thanks Vergel,

I had been dabbling with the Offset phrase but with little success, but will
now try you great suggestion. Thanks heaps.

Andrew

"Andrew Glennie" wrote:

Thanks Vergel,

Thing is that the loop works with rngFirstCell as written. But I think I
cans ee where you are coming from. rngCurrentCell won't work because some
cells in this column are blank.

Regards

Andrew

"Vergel Adriano" wrote:

Andrew,

In your Do loop, rngFirstCell is not re-assigned to any cell and is
therefore always pointing to A15 and causes an infinte loop. By the time you
reach the last row, rngCurrentCell.Offset(1,0) will throw an error. Perhaps,
you meant to use rngCurrentCell? i.e.,

Do While Not IsEmpty(rngCurrentCell)



--
Hope that helps.

Vergel Adriano


"Andrew Glennie" wrote:

Hi All,

Here is the code. I am referencing 2 different columns because the column
which is doing the selection is sometime blank so the loop needs to reference
a column with data in every row.

The line the error message appears on is:

Set rngNextCell = rngCurrentCell.Offset(1, 0)

Sub Select_Level_4()

strColumnRange = "M15"
str1stColumnRange = "A15"

Sheets("Level 4 TBA").Activate
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Clear
Range("A1").Select

Sheets("Staff Training").Activate

Set rngCurrentCell = Worksheets("Staff Training").Range(strColumnRange)
Set rngFirstCell = Worksheets("Staff Training").Range(str1stColumnRange)
Do While Not IsEmpty(rngFirstCell)
Set rngNextCell = rngCurrentCell.Offset(1, 0)
If rngCurrentCell.Value = "TBA" Then
rngCurrentCell.EntireRow.Copy
Sheets("Level 4 TBA").Activate
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.PasteSpecial xlPasteValues
End If
Set rngCurrentCell = rngNextCell
Loop
End Sub

TIA

Andrew