Anyone know how to detect a column or row insert?

R

roybrew

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
 
O

Otto Moehrbach

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
 
R

roybrew

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.
 
R

roybrew

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
 
R

roybrew

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.
 
Top