Hi.

I just posted yesterday regarding a named range anomaly, and Garry
(GS) responded with the solution to fixing my named ranges. Thanks
Garry!

The intial reason for the named ranges question, though, was because I
am encountering a massive time delay to just insert or delete a row
(approximately 5 minutes or more!!) I changed the named ranges
Garry's guidance, and everything seemed to be working fine. ...except
for the continued massive time drag to insert or delete rows.

To check things, I first copied the sheet to a new book and tried
deleting a row - no delay at all. This told me that maybe it has
something to do with either conditional formatting and/or named
ranges. So on the original sheet, I removed all conditional
formatting, named ranges, and all worksheet vba code (I have
worksheet_change code that added a hyperlink to a project number when
it was entered). I then selected all blank rows/cells and cleared
contents, and I ensured data filtering was not turned on. But when I
tried to delete a row, it was still EXTREMELY slow (same .

What else might I need to look for to hopefully fix this? It wasn't
always like this. What might have changed to cause this, and how
might I fix it? I thought about just copying all the worksheets (and
VBA modules) into a new workbook, but I'm worried about link issues
then. Any thoughts? Thanks!

Frank

Hi Frank,

Am Sat, 16 Apr 2016 09:06:32 -0400 schrieb Phrank:

What else might I need to look for to hopefully fix this? It wasn't
always like this. What might have changed to cause this, and how
might I fix it? I thought about just copying all the worksheets (and
VBA modules) into a new workbook, but I'm worried about link issues
then. Any thoughts? Thanks!

Name Manager = Select your name and put the cursor in the formula bar
of "Refers to". Your named range get a running border. Is your range
correct?
Insert in your Worksheet_Change code:
If target.count 1 then exit sub.

If that doesn't help, please upload your workbook and post the link
here.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Good morning,

I've finally been able to get back to this workbook and take a look
again. This is also tied to a post in the programming forum regarding
very slow copying of formats.

So first, I did check the Worksheet_Change code, and I already have
the line If Target.Count 1 Then Exit Sub.

Regarding the named ranges, I set them up per Garry's instructions,
and yes, when I put the cursor in the Refers To field, I get a running
boarder around my named range. ...generally. I encountered one
anomaly. I've set a named range for an offset header
(INVFunctionsHdr, which is $B$1), and a named range for the last
column (INVFunctionsLastCol, which is $J$1). This gives me the named
range formula shown below. When I put my cursor in the
INVFunctionsHdr, the running boarder is around B1, as expected. When
I select INVFunctionsLastCol and put my cursor in Refers To, the
running boarder is around J1, again as expected. But when I select
the main INVFunctions named range (as shown below), the running
boarder extends from columns B to column K.

=OFFSET(INVFunctionsHdr,1,0,COUNTA(QueryBuster!$B: $B)-1,COLUMN(INVFunctionsLastCol))

Also, nothing has changed with respect to the SLOW delete, insert, or
copying formats.

I've uploaded the workbook to my OneDrive account, and below is the
link. I would GREATLY appreciate any time spent and advice to rectify
the issues.

https://onedrive.live.com/redir?resi...nt=file%2cxlsm

Thank you!!

Frank


On Sat, 16 Apr 2016 15:15:24 +0200, Claus Busch
wrote:

Hi Frank,

Am Sat, 16 Apr 2016 09:06:32 -0400 schrieb Phrank:

What else might I need to look for to hopefully fix this? It wasn't
always like this. What might have changed to cause this, and how
might I fix it? I thought about just copying all the worksheets (and
VBA modules) into a new workbook, but I'm worried about link issues
then. Any thoughts? Thanks!

Name Manager = Select your name and put the cursor in the formula bar
of "Refers to". Your named range get a running border. Is your range
correct?
Insert in your Worksheet_Change code:
If target.count 1 then exit sub.

If that doesn't help, please upload your workbook and post the link
here.


Regards
Claus B.

Hi Frank,

Am Tue, 19 Apr 2016 07:28:19 -0400 schrieb Phrank:

Regarding the named ranges, I set them up per Garry's instructions,
and yes, when I put the cursor in the Refers To field, I get a running
boarder around my named range. ...generally. I encountered one
anomaly. I've set a named range for an offset header
(INVFunctionsHdr, which is $B$1), and a named range for the last
column (INVFunctionsLastCol, which is $J$1). This gives me the named
range formula shown below. When I put my cursor in the
INVFunctionsHdr, the running boarder is around B1, as expected. When
I select INVFunctionsLastCol and put my cursor in Refers To, the
running boarder is around J1, again as expected. But when I select
the main INVFunctions named range (as shown below), the running
boarder extends from columns B to column K.

column (INVFunctionsLastCol, which is $J$1) is column 10. With your
formula you extend the range to 10 columns. So you start in B1 this will
be column K. You have to substract 1. (I changed this in the formula)

Also, nothing has changed with respect to the SLOW delete, insert, or
copying formats.

I changed the code and the formula for # INV. But there is no change in
performance. So your workbook also takes long time to open I guess it is
corrupt and you have to create it new.
I inserted code to delete rows. If you select a whole row there will
popup a message box where you can choose if the row should be deleted.
The row is not deleted but the contents are deleted and the table will
be sorted to get rid of the blank row. Don't insert new rows. Write the
data below the table and sort the table.
In this way you can work a little faster with the workbook for the time
you need to create it new.
Look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for your workbook to see the changes.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Thank you for all of this. I now understand why the last selected
column was going to K, and the clear contents resort works great!

I'll rebuild my workbook. Is there anything that I need to watch for
or be careful of when rebuilding? And what might cause a workbook to
become corrupt?

Thank you so much again for your time and help with this!

Frank

On Tue, 19 Apr 2016 15:32:43 +0200, Claus Busch
wrote:

Hi Frank,

Am Tue, 19 Apr 2016 07:28:19 -0400 schrieb Phrank:

Regarding the named ranges, I set them up per Garry's instructions,
and yes, when I put the cursor in the Refers To field, I get a running
boarder around my named range. ...generally. I encountered one
anomaly. I've set a named range for an offset header
(INVFunctionsHdr, which is $B$1), and a named range for the last
column (INVFunctionsLastCol, which is $J$1). This gives me the named
range formula shown below. When I put my cursor in the
INVFunctionsHdr, the running boarder is around B1, as expected. When
I select INVFunctionsLastCol and put my cursor in Refers To, the
running boarder is around J1, again as expected. But when I select
the main INVFunctions named range (as shown below), the running
boarder extends from columns B to column K.

column (INVFunctionsLastCol, which is $J$1) is column 10. With your
formula you extend the range to 10 columns. So you start in B1 this will
be column K. You have to substract 1. (I changed this in the formula)

Also, nothing has changed with respect to the SLOW delete, insert, or
copying formats.

I changed the code and the formula for # INV. But there is no change in
performance. So your workbook also takes long time to open I guess it is
corrupt and you have to create it new.
I inserted code to delete rows. If you select a whole row there will
popup a message box where you can choose if the row should be deleted.
The row is not deleted but the contents are deleted and the table will
be sorted to get rid of the blank row. Don't insert new rows. Write the
data below the table and sort the table.
In this way you can work a little faster with the workbook for the time
you need to create it new.
Look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for your workbook to see the changes.


Regards
Claus B.

Hi Frank,

Am Tue, 19 Apr 2016 17:34:44 -0400 schrieb Phrank:

I'll rebuild my workbook. Is there anything that I need to watch for
or be careful of when rebuilding? And what might cause a workbook to
become corrupt?

I don't know why workooks become corrupt.
Have a look in the VBA project explorer. There is one sheet more than in
the workbook (Sheet1). Perhaps it helps if you move all sheets one by
one into a new workbook. If you select all sheets and move them together
that wrong sheet will be moved also.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional