Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 91 - Object variable with block variable not set
I get the above error at line:
Range(rng.Rows(startrow), rng.Rows(endrow - 1)).Delete I wrote the code in Excel 2003 but try to run it in 97 and had to make a few chnages. I dont know why I am getting error 91 at the above line though. Anyone able to help? Full code posted below (its only short) -Al Sub Sto() ' Import Sto Data 'Sets name of file to open FName = Application.GetOpenFilename(FileFilter:="Text File (*.txt), FilterIndex:=2") 'Opens file with Text Wizard Workbooks.OpenText FileName:=FName, Origin:=xlMSDOS, _ startrow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(7, _ 1), Array(14, 1), Array(28, 1), Array(36, 1), Array(39, 1), Array(42, 1), Array(44, 1), _ Array(62, 1), Array(81, 1), Array(89, 1), Array(101, 1), Array(104, 1), Array(110, 1), _ Array(119, 1), Array(122, 1), Array(130, 1)) ' Selectsand Sorts on Column A Columns("A:Q").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Set sh = Sheets(1) Set rng = sh.[a1].CurrentRegion Tag = "PAGE:" num = Application.CountIf(rng, Tag) startrow = rng.Find(What:=Tag).Row MsgBox startrow.Row endrow = startrow + num Range(rng.Rows(startrow), rng.Rows(endrow - 1)).Delete Tag = "REG" num = Application.CountIf(rng, Tag) startrow = rng.Find(Tag, LookAt:=xlWhole).Row endrow = startrow + num Range(rng.Rows(startrow), rng.Rows(endrow - 1)).Delete Set rng = sh.[h1].CurrentRegion Tag = "V" num = Application.CountIf(rng, Tag) startrow = rng.Find(Tag, LookAt:=xlWhole).Row - 1 endrow = startrow + 1000 Range(rng.Rows(startrow), rng.Rows(endrow - 1)).Delete End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 91 - Object variable with block variable not set
Jim,
Try replacing the comma with a colon Range is looking for something like ("A1:D77") HTH Henry "Jim" wrote in message ... I get the above error at line: Range(rng.Rows(startrow), rng.Rows(endrow - 1)).Delete I wrote the code in Excel 2003 but try to run it in 97 and had to make a few chnages. I dont know why I am getting error 91 at the above line though. Anyone able to help? Full code posted below (its only short) -Al Sub Sto() ' Import Sto Data 'Sets name of file to open FName = Application.GetOpenFilename(FileFilter:="Text File (*.txt), FilterIndex:=2") 'Opens file with Text Wizard Workbooks.OpenText FileName:=FName, Origin:=xlMSDOS, _ startrow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(7, _ 1), Array(14, 1), Array(28, 1), Array(36, 1), Array(39, 1), Array(42, 1), Array(44, 1), _ Array(62, 1), Array(81, 1), Array(89, 1), Array(101, 1), Array(104, 1), Array(110, 1), _ Array(119, 1), Array(122, 1), Array(130, 1)) ' Selectsand Sorts on Column A Columns("A:Q").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Set sh = Sheets(1) Set rng = sh.[a1].CurrentRegion Tag = "PAGE:" num = Application.CountIf(rng, Tag) startrow = rng.Find(What:=Tag).Row MsgBox startrow.Row endrow = startrow + num Range(rng.Rows(startrow), rng.Rows(endrow - 1)).Delete Tag = "REG" num = Application.CountIf(rng, Tag) startrow = rng.Find(Tag, LookAt:=xlWhole).Row endrow = startrow + num Range(rng.Rows(startrow), rng.Rows(endrow - 1)).Delete Set rng = sh.[h1].CurrentRegion Tag = "V" num = Application.CountIf(rng, Tag) startrow = rng.Find(Tag, LookAt:=xlWhole).Row - 1 endrow = startrow + 1000 Range(rng.Rows(startrow), rng.Rows(endrow - 1)).Delete End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 91 - Object variable with block variable not set
you could also try Rows(rng.Rows(startrow) & ":" & rng.Rows(endrow - 1)).Delet Shift:=xlU ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
Run time error '91' object variable-explain in plain english?? | Excel Discussion (Misc queries) | |||
object variable or with block variable not set | Excel Discussion (Misc queries) | |||
Object Variable or With Block variable not set? | Excel Programming |