Need help resolving error in defining last row
 

Help...inexperienced Excel VB programmer at work...

I can't seem to figure out why my code below generates an error at the
"LastCol" definition line. Can anyone help me out? Thanks!

Also, as you can probably tell from the code, I am wanting to clear
cells in the range that contain certain text - is there a simpler way
to do this, rather than having each separate If/EndIf section?


Dim DataTable As Range
Dim LastRow As Long
Dim LastCol As Long

Application.ScreenUpdating = False
Sheets("Training Record").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
LastRow = Range("A10").End(xlDown).Row
LastCol = Range("K1").End(x1toright).Column
Set DataTable = Range("K10", cells(LastRow & LastCol))

For Each cell In DataTable
If cell.Value = "a/r" Then
cell.ClearContents
End If

If cell.Value = "core" Then
cell.ClearContents
End If

If cell.Value = "inv" Then
cell.ClearContents
End If

If cell.Value = "opt" Then
cell.ClearContents
End If

Next


--
clmarquez
------------------------------------------------------------------------
clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386
View this thread: http://www.excelforum.com/showthread...hreadid=494767

Need help resolving error in defining last row
 

On Mon, 19 Dec 2005 19:00:14 -0600, clmarquez
wrote:

LastCol = Range("K1").End(x1toright).Column

Spelling! It should be xlToRight (you have the number 'one' instead
of the letter 'l')

Need help resolving error in defining last row
 

You could try:

Dim DataTable As Range
Dim LastRow As Long
Dim LastCol As Long

Application.ScreenUpdating = False
Sheets("Training Record").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
LastRow = Range("A10").End(xlDown).Row
LastCol = Range("K1").End(xlToRight).Column
Set DataTable = Range("K10", Cells(LastRow & LastCol))

For Each cell In DataTable
Select Case cell.Value
Case "a/r", "core", "inv", "opt"
cell.ClearContents
End Select

Next cell

Hope this helps
Rowan

clmarquez wrote:
Help...inexperienced Excel VB programmer at work...

I can't seem to figure out why my code below generates an error at the
"LastCol" definition line. Can anyone help me out? Thanks!

Also, as you can probably tell from the code, I am wanting to clear
cells in the range that contain certain text - is there a simpler way
to do this, rather than having each separate If/EndIf section?


Dim DataTable As Range
Dim LastRow As Long
Dim LastCol As Long

Application.ScreenUpdating = False
Sheets("Training Record").Select
ActiveSheet.Outline.ShowLevels RowLevels:=2, ColumnLevels:=2
LastRow = Range("A10").End(xlDown).Row
LastCol = Range("K1").End(x1toright).Column
Set DataTable = Range("K10", cells(LastRow & LastCol))

For Each cell In DataTable
If cell.Value = "a/r" Then
cell.ClearContents
End If

If cell.Value = "core" Then
cell.ClearContents
End If

If cell.Value = "inv" Then
cell.ClearContents
End If

If cell.Value = "opt" Then
cell.ClearContents
End If

Next

Need help resolving error in defining last row
 

I guess I should copy and paste code, rather than type it in! The "1"
looks just like the letter "l" in the VB window...


--
clmarquez
------------------------------------------------------------------------
clmarquez's Profile: http://www.excelforum.com/member.php...o&userid=29386
View this thread: http://www.excelforum.com/showthread...hreadid=494767