Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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') |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help Resolving Circular Ref | Excel Worksheet Functions | |||
Vlookup not Resolving | Excel Worksheet Functions | |||
Anticipating and resolving conflict | New Users to Excel | |||
Error 1004 when defining range by cells(r,c) format | Excel Programming | |||
resolving reference - Range of what? | Excel Programming |