Does anyone happen to have a row insert function?

If Row is inserted = True

Thank you

Mark

Please can you supply a bit more information:
Looking at the spread sheet, how would one know that a row had been insterted?
Or do you have occasions when the macro command to insert a row does not work?

"ML" wrote:


Does anyone happen to have a row insert function?

If Row is inserted = True

Thank you

Mark

Hi Gleam

I am not sure precisely how I am going to approach the code yet.
If I had a Boolean function that is true when a row is inserted, I can
possibly work it out from there.

I am trying to figure out how to change some code dynamically based on if a
row is inserted

Mark



"Gleam" wrote:

Please can you supply a bit more information:
Looking at the spread sheet, how would one know that a row had been insterted?
Or do you have occasions when the macro command to insert a row does not work?

"ML" wrote:


Does anyone happen to have a row insert function?

If Row is inserted = True

Thank you

Mark

even if the code were based on a worksheet_change, the worksheet
wouldn't necessarily know WHAT had changed. the only way i can see to
do this (non-guru person here) would be to have a counter somewhere on
a hidden sheet or in a hidden column that kept track of how many rows
you were working with and could thereby track whether or not that
number had changed.......
and even then you'd have to have the original number stored also so it
could compare them. unless it's a constant sized spreadsheet that is
supposed to never get any larger & you're looking for whether or not a
user has slipped an extra row in there.
just ideas
:)
susan



On Nov 28, 12:07 pm, ML wrote:
Hi Gleam

I am not sure precisely how I am going to approach the code yet.
If I had a Boolean function that is true when a row is inserted, I can
possibly work it out from there.

I am trying to figure out how to change some code dynamically based on if a
row is inserted

Mark



"Gleam" wrote:
Please can you supply a bit more information:
Looking at the spread sheet, how would one know that a row had been insterted?
Or do you have occasions when the macro command to insert a row does not work?

"ML" wrote:

Does anyone happen to have a row insert function?

If Row is inserted = True

Thank you

Mark- Hide quoted text -

- Show quoted text -

Hi Susan
I am primarily an AutoCAD-VBA programmer, however I dablle with Excel on
occasion, so please excuse me if I am not a guru either :)

OK, let me think of this in pseudo code:

We actually have a counter; it is The number of used rows in a given range.
So, this part does not seem that difficult pseudo wise.
If user inserts a row within this range; it is rowinsert + 1

The way I see it is, if The formulas can adjust dynaically in worksheet
based on an insert event, then macros would be able to also.
This would likely require a loop an an array of some sort.

Mark

"Susan" wrote:

even if the code were based on a worksheet_change, the worksheet
wouldn't necessarily know WHAT had changed. the only way i can see to
do this (non-guru person here) would be to have a counter somewhere on
a hidden sheet or in a hidden column that kept track of how many rows
you were working with and could thereby track whether or not that
number had changed.......
and even then you'd have to have the original number stored also so it
could compare them. unless it's a constant sized spreadsheet that is
supposed to never get any larger & you're looking for whether or not a
user has slipped an extra row in there.
just ideas
:)
susan



On Nov 28, 12:07 pm, ML wrote:
Hi Gleam

I am not sure precisely how I am going to approach the code yet.
If I had a Boolean function that is true when a row is inserted, I can
possibly work it out from there.

I am trying to figure out how to change some code dynamically based on if a
row is inserted

Mark



"Gleam" wrote:
Please can you supply a bit more information:
Looking at the spread sheet, how would one know that a row had been insterted?
Or do you have occasions when the macro command to insert a row does not work?

"ML" wrote:

Does anyone happen to have a row insert function?

If Row is inserted = True

Thank you

Mark- Hide quoted text -

- Show quoted text -

There are three ways that I know to count rows. The one I would probably use is
Nrows=activecell.specialcells(xlLastcell).row
This is similar to doing control end on a spread sheet.

Number 2 is
Nrows=activesheet.usedrange.rows.count
This will give the same answer if there are no blank rows at the start. If
rows 1 and 2 are blank then it will give an answer 2 less than the first
method.

Number 3 is
Nrows = range("B47").currentregion.rows.count
This will give the number of rows in the continuous region around cell B47

To dynamically link to changes on the worksheet you need a macro on the
sheet. In the VBA editor please double click on the sheet and try this code:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.UsedRange ' This line is needed to get correct answer when
rows
' are deleted.
MsgBox "No of used rows =" & ActiveCell.SpecialCells(xlLastCell).Row
End Sub

This works for inserting rows.
It doesn't work if the user deletes rows, unless the user also saves the
file! (in Excel 2003)

"ML" wrote:

Hi Susan
I am primarily an AutoCAD-VBA programmer, however I dablle with Excel on
occasion, so please excuse me if I am not a guru either :)

OK, let me think of this in pseudo code:

We actually have a counter; it is The number of used rows in a given range.
So, this part does not seem that difficult pseudo wise.
If user inserts a row within this range; it is rowinsert + 1

The way I see it is, if The formulas can adjust dynaically in worksheet
based on an insert event, then macros would be able to also.
This would likely require a loop an an array of some sort.

Mark

"Susan" wrote:

even if the code were based on a worksheet_change, the worksheet
wouldn't necessarily know WHAT had changed. the only way i can see to
do this (non-guru person here) would be to have a counter somewhere on
a hidden sheet or in a hidden column that kept track of how many rows
you were working with and could thereby track whether or not that
number had changed.......
and even then you'd have to have the original number stored also so it
could compare them. unless it's a constant sized spreadsheet that is
supposed to never get any larger & you're looking for whether or not a
user has slipped an extra row in there.
just ideas
:)
susan



On Nov 28, 12:07 pm, ML wrote:
Hi Gleam

I am not sure precisely how I am going to approach the code yet.
If I had a Boolean function that is true when a row is inserted, I can
possibly work it out from there.

I am trying to figure out how to change some code dynamically based on if a
row is inserted

Mark



"Gleam" wrote:
Please can you supply a bit more information:
Looking at the spread sheet, how would one know that a row had been insterted?
Or do you have occasions when the macro command to insert a row does not work?

"ML" wrote:

Does anyone happen to have a row insert function?

If Row is inserted = True

Thank you

Mark- Hide quoted text -

- Show quoted text -

Hi Gleam
I agree with you on all levels; as I said before, I am primarily an
AutoCAD-VBA programmer and I dablle with Excel programming; although I do
like it a lot :)

I read everything you wrote, now let me go try your code, then get back

Thank you!

Mark

"Gleam" wrote:

Please can you supply a bit more information:
Looking at the spread sheet, how would one know that a row had been insterted?
Or do you have occasions when the macro command to insert a row does not work?

"ML" wrote:


Does anyone happen to have a row insert function?

If Row is inserted = True

Thank you

Mark