Anyone know how to detect a column or row insert?

Does anyone know how to detect a column or row insert event other than
doing some complicated processing on every Worksheet_Change event?  I'm
looking for something a little cleaner than that.  I realize that this
might be a stretch.  Thanks in advance.

Roy Berger

0
roybrew (35)
2/20/2005 8:16:24 PM
excel 39879 articles. 2 followers. Follow

4 Replies
631 Views

Similar Articles

[PageSpeed] 53

Roy
    To detect a change in the contents of a cell is going to take an event 
macro of one type or another.  Event macros are really not very complicated 
at all.  Post back and provide some details about what you want to do and 
you'll get plenty of help.  HTH  Otto
<roybrew@att.net> wrote in message 
news:1108930584.641235.20690@f14g2000cwb.googlegroups.com...
> Does anyone know how to detect a column or row insert event other than
> doing some complicated processing on every Worksheet_Change event?  I'm
> looking for something a little cleaner than that.  I realize that this
> might be a stretch.  Thanks in advance.
>
> Roy Berger
> 


0
ottokmnop (389)
2/20/2005 8:53:51 PM
Well, I want to detect a column or row insert and see if it falls
within a certain range.  I really don't care about the contents of a
cell changing.  I know how to do that.  I just wondered if there was
some easy way to detect a column or row insert.

0
roybrew (35)
2/21/2005 6:38:30 PM
I figured it out.  Here's the Worksheet Change Event code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim maxrows As Long
    Dim maxcols As Long

    maxcols = 256
    maxrows = 65536

    If (Target.Rows.Count = maxrows) Then           ' **** Indicates
Column Insert ****
        MsgBox "A Column Insert for " & Target.Columns.Count & _
            " columns was detected at " & Target.Address
    ElseIf (Target.Columns.Count = maxcols) Then    ' **** Indicates
Row Insert ****
        MsgBox "A Row Insert for " & Target.Rows.Count & _
            " columns was detected at " & Target.Address
    Else
        MsgBox "Some other kind of change event happened"
    End If
End Sub

0
roybrew (35)
2/21/2005 7:16:37 PM
Unfortunately, you can trap column or row deletes in the same manner.
So I have a little more investigative work to do to distinguish between
col/row inserts and deletes.

0
roybrew (35)
2/22/2005 1:34:39 PM
Reply:

Similar Artilces:

anyone knows
presently I am using Excel 2000. For file open I use ^O or open fro file menu, a box appeared from where I can select file for open. Lef side of this box some shortcuts are there to reach a specific locatio such as "History", "Mydocument", "Desktop", "Favorities" etc. I want to add my locations (directory) so that I can reach there fast. How I can do? thanks in advance. S Kara -- Message posted from http://www.ExcelForum.com You can use the Places COM add-in from Microsoft: INFO: Office Developer Samples and Tools Available for Download http:...

Cost changes on one inserted project in master project
Project Server (PS) 2007 SP2 MSO I have a master project (MP) with several inserted projects. The MP is not stored on PS. It was built by inserting projects from PS and breaking each link. Calculate is set to manual. I open the MP which has all inserted projects collasped. I expand one and the cost changes (turns blue) in the one above it (still collasped). I calculate and it turns back to the number it was. Why? These are inserted projects, none of them roll into another. Also, often a number turns blue but there is no change. I have taken it to the 13th decimal (in e...

Help with VBE Need to autoselect rows #2
does anybody know how to do this -- greenfalco ----------------------------------------------------------------------- greenfalcon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1362 View this thread: http://www.excelforum.com/showthread.php?threadid=26168 first of all, C and D are not rows, they are columns :-)... based on activecell, if you want to select just the cells: Range(ActiveCell, ActiveCell.Offset(1, 0)).Select if you want to select the entirerow: Range(ActiveCell, ActiveCell.Offset(1, 0)).EntireRow.Selec -- icestationzbr -------------------...

Anyone?
Anyone? In news:1121087669.975314.186420@g44g2000cwa.googlegroups.com, Zack Schneeberger <schneebie1@**** > typed: > Anyone? Yes? ...

Row Deletion Question
I have a file with over 3 thousand line items. What I am trying to do is delete an entire row based off an entry in column L. Example Location Customer number CA 23432 OR 13245 TX 45321 FL 43215 I want to search for 13245 & 45321 and delete those rows. Or better yet would be to identify those I want to keep, 23432 and 43215, and delete all other rows. In this example the location column,as well as other entries in the file, means nothing it was listed here because I felt two rows would show my ex...

Detect when detail section pushes to 2nd page
Is there a way of detecting when detail section pushes to 2nd page via code. Sometimes my report group needs 2 pages and sometimes it don't, but I need a diiferent set of conditions when it does. Thanks, for any help! Wally A possibility is to count the number of records in the detail section. If the count exceeds some number depending on the design of your report, your report will go o a second page. Steve santus@penn.com "wallymeister" <wallymeister@discussions.microsoft.com> wrote in message news:6BC9EB70-6BC3-44B8-9C65-31A0655A9FBD@microsoft.co...

Anyone know what a
stick graph is? Is it similar to a candlestick graph? Kids have to do one for homework and nobody seems to know what it is! Rob Graham Hi Rob, Is it possibly a xy scatter where the points are not connected to each other but with the x axis. Looks sort of like a column chart with very thin bars. Where the x axis is a value axis and not the usual category type. If so plot your x and y values as a normal scatter chart , points only. The add negative Y error bars to the series. Use custom values and select the y values. This should then drop vertical lines to the x axis. Rob Graham wrote...

can i somehow put two cells into one column or split a column
Data>text to columns to split =A1&B1 in a third cell with concatenate 2 (or more) -- Regards, Peo Sjoblom (No private emails please) "Chrono" <Chrono@discussions.microsoft.com> wrote in message news:E211310C-11BC-423A-92AF-5F0863940E4D@microsoft.com... > ...

How to reverse the column name and row name?
In the original setting of excel, the column name starts from "A", "B", "C", .....so on and the row name starts from "1", "2", "3".... so on. Is it possible to change the setting to the column name starts from "1", "2", "3" and the row name starts from "A", "B", "C" ? Many thanks in advance ! The simple answer is no. You can change column headings to numbers by adopting the R1C1 notation Tools>Options>General and select the R1C1 style. You could use Row 1 to displ...

Anyone?
I am making a list of names that I will be ranking and need to be able to move them up and down the list frenquently in a timely mannor. How do I create a list that I can click on a name then drag and drop it into another spot on the list, as aposed to cutting and inserting? ...

Insert Picture into header
Is it possible to insert a picture or clip art into the header or footer? If so, how? We are on version 2000. Thanks, Jasper IIRC, that ability was added in XL02. In article <u$zIvbV3EHA.2156@TK2MSFTNGP10.phx.gbl>, "Jasper Recto" <jrecto99@yahoo.com> wrote: > Is it possible to insert a picture or clip art into the header or footer? > If so, how? We are on version 2000. Jasper To add to John's answer. That would depend upon your version of Excel(always good to post that info) With versions 2002 and 2003 you can insert a graphic into the Header/Footer u...

Format a whole row based on value of one cell
I want to use conditional formatting to colour a whole row of cell based on the value in column C. I understand how conditional formatting works on the single cell yo select, but I don't know how to change the format for multiple cell based on the value of a single cell. Any suggestions? g -- Message posted from http://www.ExcelForum.com Hi try the following as an example: - select row 1 - goto 'format - Conditional Format' - choose 'formula' and enter the following formula =$C1=your_value - choose a format -- Regards Frank Kabel Frankfurt, Germany > I want to u...

inverting a column of data
Hi! Everyone, I have a column of numerical data entered in (say) A1.A100. How can I reverse their order, i.e to have value in A100 to be in B1, A99 in B2, ... and A1 in B100? Appreciate any helps. Hi in B1 enter =OFFSET($A$100,-(ROW()-1),0) and copy down "LCC" wrote: > Hi! Everyone, > > I have a column of numerical data entered in (say) > A1.A100. How can I reverse their order, i.e to have value > in A100 to be in B1, A99 in B2, ... and A1 in B100? > > Appreciate any helps. > In B1 enter =OFFSET($A$1,COUNT(A1:$A$100)-1,0) and copy down to B100 ...

I need to print a B5 booklet. Anybody know of any software?
Ineed to print a booklet in B5 format. Is there any software that will let me do this in duplex? If you have Microsoft Publisher, File, Print Setup, select the paper size B5. Then File, Page Setup, select Special Fold (should show the paper as B5) and Landscape. -- Don Vancouver, USA "Arty" <Arty@discussions.microsoft.com> wrote in message news:304D78F4-EA5D-4D69-A84F-DE3A57FFCBEB@microsoft.com... > Ineed to print a booklet in B5 format. Is there any software that will let me > do this in duplex? A small child turns to Ed, and exclaims: "Look! Look! A pos...

My links become #value! when I insert a row
My links in my worksheet become #value! when I insert a row/column. Help! If I understand your problem correctly:- The #VALUE! appears on cells when it's content is too wide for th column width. Try increasing the size of the column so that the actua value can fit. Double Click the line to the right of the column lette at the top of the worksheet (the left border of what looks like th button for each column label) and it will automatically resize th column. If not just resize it to whatever size you want -- jaydevi -----------------------------------------------------------------------...

How do I create column headings in Excel in the A, B, C etc column
I have seen spreadsheets where the column heads, A, B, C etc have titles but haven't been able to figure out how to insert my own. thanks for any enlightenment on this topic. -- Elianna You can't. You can REMOVE the display of the headings and insert your own in row 1 then freeze row 1 so it always shows, but you're still really typing in columns A, B, etc. To remove the display of the headings: tools/Options/View tab, deselect Row & Column Headers To freeze row 1: Select row 2, Window/Freeze Panes. Bob Umlas Excel MVP "eliannaj" <eliannaj@discussions.microsoft...

Has anyone been able to write an if/then formula in excell?
Has anyone been able to write an If/Then formula in Excell? (e.g. If A5=21, then B35=10/21/04) Hi in cell B35 enter =IF(A5=21,DATE(2004,10,21),"") -- Regards Frank Kabel Frankfurt, Germany "KirkF" <KirkF@discussions.microsoft.com> schrieb im Newsbeitrag news:8E5F75BB-F4F3-4853-97A5-7FEB7AC4C8CB@microsoft.com... > Has anyone been able to write an If/Then formula in Excell? (e.g. If A5=21, > then B35=10/21/04) No. You can't write a formula that will affect another cell. You' need to use VBA code for that -- firefyt ----------------------------...

select the number of rows using shortcut keys
Not able to select the number of rows using shortcut keys (SHIFT+DOWNARROW OR UPARROW) So what happens when you hit SHIFT + Downarrow? Should move down and select the next row as well as start row. SHIFT + END + Downarrow should select to next blank row. Gord Dibben Excel MVP On Wed, 10 Nov 2004 05:03:01 -0800, Naidu Samudrala <Naidu Samudrala@discussions.microsoft.com> wrote: >Not able to select the number of rows using shortcut keys (SHIFT+DOWNARROW OR >UPARROW) ...

Change field type to lookup column
Can you (and if so, how?) change the data type of a populated field to lookup from a table without losing all the previously input data? -- savannah Do not change the field in the table but in your form use a list box. -- KARL DEWEY Build a little - Test a little "savannah" wrote: > Can you (and if so, how?) change the data type of a populated field to lookup > from a table without losing all the previously input data? > -- > savannah Thanks Karl. However, forms are not being used at this time. Data is entered directly on the form. I do understand that this f...

How do I change the color of the Row and Column ID highlight colo.
How do I change the color of the Row and Column ID highlight color from orange to another color in Excel 2003? high light the row/column whose color you wish to change format>cells>pattern tab. pick color. click ok. >-----Original Message----- >How do I change the color of the Row and Column ID highlight color from >orange to another color in Excel 2003? >. > Ashman Right-click on Desktop and "Properties" Display settings for appearance, themes, desktop etc. are found here. In your case, look to Themes.......you probably have it set to "Wheat"...

Don't know how to organize info I have.
I am trying to organize info in regards to when the most people are best able to gather at the same time to complete a certain task. I had everybody give me the times they are best able to be there to complete this task (based on 24 hours) for each day of the week (Sunday through Saturday). What I want to do is create some type of table/chart where I can take all this info and show each person and the times they are avaiable. From there, I want it to be able to compile these times together and show me which hours and which people are able to come together for this task each day of the w...

Sum on Rounded column
Hello I have a column called 'Area' in my table which is rounded to 2 decimal places. The data to be stored in this column is rounded to 2 decimal places and then only inserting to the table. If I take a sum of this column the result is not rounding to 2 decimal places. Why is that? How is sum implemented on Float data types in SQL Server? Thanks Baiju Baiju K U (baiju@indus-systems.com) writes: > I have a column called 'Area' in my table which is rounded to 2 decimal > places. The data to be stored in this column is rounded to 2 decimal > pla...

Using Data Validation in one column, based on contents of second column
Hello, This is the first time i've ever posted on this board, so bear with me. I am trying to add data validation to one column, that checks the value of another column in the same form, and if the value is true, not allow a 0 to be entered in the column. For example, Column 1 = Type, Column 2 = Usage. If Column1 = Gas, Electric, Water, or Sewer, Then Column2 should not allow a 0 value. If Column1 = Fire, Garbage, Sanitation Then Column2 should allow a zero value. Currently my validation is >0 which works, but doesn't filter its validation based on column1, only on the value of ...

i sent my messag i know
fram mi MACALIM AADAM;1220408 Wrote: > fram mi If you do have a valid question, we certainly need more information. -- richc46 RICHARD *If I have helped you in any way, please show your appreciation, by clicking the middle icon, on the upper right portion of your screen.* *Before you take my advice make a System Restore point.* ...

Text-to-Columns Stuck
On pasting text, the Text to Columns operation happens without being asked for. Apparently it sticks from a previous operation. How can I shut it off, so I can manipulate the data first and then choose among the various options to get columns? The only way I can prevent it now is to open another application. AFAIK, there is no builtin Excel capabillity to do this. Here is a macro that I use to deal with that issue. Sub FixTextToColumns() '' Fixes Excel's memory when it parses pasted text automatically. Dim rngO As Range Application.ScreenUpdating = False O...