artds
11-26-2007, 12:38 AM
HI all!!
I have this minor problem which just seemed to bug me, was wondering if any of you can help. Please take a look at the following codes:
Sub ExpandTables()
Dim TblNm As Range, n As Name
Dim cnt As Integer
Dim ws As Worksheet
Set ws = Sheet9
ws.Activate
cntDistiRow = Cells(1, 1).Value
For Each n In ws.Names
Set TblNm = Range(n)
cnt = TblNm.Rows.count
Do While Not (cnt - 2) < cntDistiRow
TblNm.Rows(cnt).EntireRow.Offset(-1, 0).Select
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
cnt = cnt + 1
Loop
Next n
End Sub
Now, there's no problem with the code... Just that, in the VBE window, i use the 'f8' key to step in to the procedure, and when line reached to "Set TblNm=range(n)", and i point my mouse cursor to 'n', it indicate a name range, "=summary!$A$13:$A$15".
Now the thing is, I've looked through all the sheets in that workbook, and there are no such name ranges. I definately did not define that particular name range, so how did it get there? I want to delete the name range, but in the "Define Name" window, I cannot find such name. When I tried to delete the above mentioned cells, and again 'f8' the procedure, it indicate "=summary!#REF!". However rows are still inserted to that particular range.
There are no other open workbooks, and no hidden sheets. So I'm just wondering if theres anyone who can help me overcome this problem. As If i leave it as it is, the code will ruin my entire format. as there are other name ranges, which I did add myself, in the rows below.
Thanks all in advance
regards
artds
I have this minor problem which just seemed to bug me, was wondering if any of you can help. Please take a look at the following codes:
Sub ExpandTables()
Dim TblNm As Range, n As Name
Dim cnt As Integer
Dim ws As Worksheet
Set ws = Sheet9
ws.Activate
cntDistiRow = Cells(1, 1).Value
For Each n In ws.Names
Set TblNm = Range(n)
cnt = TblNm.Rows.count
Do While Not (cnt - 2) < cntDistiRow
TblNm.Rows(cnt).EntireRow.Offset(-1, 0).Select
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
cnt = cnt + 1
Loop
Next n
End Sub
Now, there's no problem with the code... Just that, in the VBE window, i use the 'f8' key to step in to the procedure, and when line reached to "Set TblNm=range(n)", and i point my mouse cursor to 'n', it indicate a name range, "=summary!$A$13:$A$15".
Now the thing is, I've looked through all the sheets in that workbook, and there are no such name ranges. I definately did not define that particular name range, so how did it get there? I want to delete the name range, but in the "Define Name" window, I cannot find such name. When I tried to delete the above mentioned cells, and again 'f8' the procedure, it indicate "=summary!#REF!". However rows are still inserted to that particular range.
There are no other open workbooks, and no hidden sheets. So I'm just wondering if theres anyone who can help me overcome this problem. As If i leave it as it is, the code will ruin my entire format. as there are other name ranges, which I did add myself, in the rows below.
Thanks all in advance
regards
artds