How to keep Inserting Rows Command from moving other cells in a sheet

[Ladyj205]

I have 2 command buttons on excel 2010.
everytime i click on the commandbuttons for each the other tables and cells seems be to effected. like everything is out of place. is there a vba for my vba codes to keep the other cells still while the user select the new row button.

the codes i have for my command buttons:

Private Sub CommandButton2_Click()
Sheets("Sheet1").Range("A32").Select
ActiveCell.EntireRow.Insert Shift:=xlDown


Sheets("Sheet1").Range("A32:D32").Select
Selection.Borders.Weight = xlThin
End Sub


Private Sub CommandButton1_Click()
Sheets("Sheet1").Range("A15").Select
ActiveCell.EntireRow.Insert Shift:=xlDown


Sheets("Sheet1").Range("A15:D15").Select
Selection.Borders.Weight = xlThin


End Sub

[Paul_Hossler]

That's what InsertRows does -- pushs everything down

You could save the row number above the data you do not want to move, insert the row with your macro, and then delete the saved row number

[Ladyj205]

That's what InsertRows does -- pushs everything down

You could save the row number above the data you do not want to move, insert the row with your macro, and then delete the saved row number

how can insert the rows with macros, how can i do that??

[Ladyj205]

i was trying to get my rows to look like this. everytime i click new row. it moves everthing else

[Paul_Hossler]

It looks like your 2 CommandButtons insert a new row 15 and a new row 32, but you don't want anything pushed down???

This inserts a new row at 15, and deletes the old row at 18 which had been 17

Option Explicit

Private Sub CommandButton1_Click()
    Me.Rows("15:15").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Me.Rows("18:18").Delete Shift:=xlUp
End Sub

If you want anything other that that, you'll need to explain a lot more with an attachment and examples

[Ladyj205]

i was saying i have a couple of other stuff on the sheet. its moving everything down and messing up my optionbuttons and causing them not to work. i was trying to see if there is someway i can have set to a limited of rows when u click on the command button.

[Ladyj205]

is there a way to keep the row instead of deleting ----->>> Me.Rows("18:18").Delete Shift:=xlUp

because everytime i click the command button and it overrides and deletes my other tables thats under it.

[Paul_Hossler]

is there a way to keep the row instead of deleting ----->>> Me.Rows("18:18").Delete Shift:=xlUp

because everytime i click the command button and it overrides and deletes my other tables thats under it.

I can't figure out what you're wanting to do.

Post an example workbook with a description of what you'd like to end up with

[SamT]

Set your Option Buttons and other controls to NOT move with cells

Guessing about your columns
Range("B17:C17").Insert w/Shift Down
Range("B18:C18").Delete w/Shift Up

[Ladyj205]

i was saying i have a couple of other stuff on the sheet. its moving everything down and messing up my optionbuttons and causing them not to work. i was trying to see if there is someway i can have set to a limited of rows when u click on the command button.

Set your Option Buttons and other controls to NOT move with cells

Guessing about your columns
Range("B17:C17").Insert w/Shift Down
Range("B18:C18").Delete w/Shift Up

so it will be like:

Private Sub CommandButton1_Click()
Range("38:38").Insert Shift:=xlDown,
Range("46:46").Delete Shift:=xlUp
End Sub

[Ladyj205]

I can't figure out what you're wanting to do.

Post an example workbook with a description of what you'd like to end up with

all i was saying was adding rows affects other tables and they were added to the wrong section. you on the right path with me. i was trying to figure how prevent the clashing with my other materials and deleting on my sheet. everytime the new row button has been click. i wanted the user to click the new row button as much they need without messing with other material. sorry for being difficult.


i insert what problem i keep running into

thank u

[SamT]

so it will be like:

Private Sub CommandButton1_Click()
Range("38:38").Insert Shift:=xlDown,
Range("46:46").Delete Shift:=xlUp
End Sub

That is the same as

Private Sub CommandButton1_Click()
Rows(38).Insert
Rows(46).Delete
End Sub

Insert and Delete Rows is always Shift down and shift up, respectively. How can be otherwise?

I was trying to show you how to Insert and Delete only part of a row. You only need to specify the Shift direction when working on part of a Row.

We have no idea what your worksheet looks like, so we can not give you any precise advice. Your pretty pictures don't carry any usable information at all.

You can upload a workbook by clicking the Go Advanced button and the Manage Attachments button on that page

[Paul_Hossler]

i insert what problem i keep running into

thank u

It would be a lot easier to suggest options if you would attach a workbook like people have suggested

[Ladyj205]

sorry . i hope this helps

[Paul_Hossler]

OK, using your workbook, I clicked [New Row] two times ...

Private Sub CommandButton1_Click()
Me.Rows("16:16").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Me.Rows("26:26").Delete Shift:=xlUp
End Sub

... and it inserted a new row 16 and deleted row 26 and then another new row 16 and then deleted row 26 again

That all seems reasonable to me -- it did what you asked it to do


Capture.JPG


Now I do see 4 empty Group boxes on the worksheet and a lot of Option Button code ….

Private Sub OptionButton_1_0_Click()
  If OptionButton_1_0 Then
  Range("D60").Value = 0
  End If
End Sub

Private Sub OptionButton_1_1_Click()
  If OptionButton_1_1 Then
   Range("D60").Value = 25
  End If
End Sub

… so I suspect that there are some things on your worksheet that were not in your attachment???

[Ladyj205]

yes it was somethings on my worksheet not mention. i was trying to re-do the whole thing. i forgot to take it out. sorry if there anyway to overide the new rows to keep it from deleting other rows.

[Paul_Hossler]

yes it was somethings on my worksheet not mention. i was trying to re-do the whole thing. i forgot to take it out. sorry if there anyway to overide the new rows to keep it from deleting other rows.

Don't know what 'override the new rows' means

If you insert a row like that, then it will push the other rows down, as well as any controls unless you mark each control to "Don't Move or Resize with Cells" like SamT said in #9

Capture.JPG


If you just want to INSERT a new row 16 without deleting, just remove the second like

Private Sub CommandButton1_Click()

Me.Rows("16:16").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Me.Rows("26:26").Delete Shift:=xlUp

End Sub

If you want to REPLACE row 16 then maybe

Private Sub CommandButton1_Click()

Me.Rows("16:16").Delete Shift:=xlUp
Me.Rows("15:15").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

[Ladyj205]

is there a way i can hide a number of rows if the user wanted to use a lot of rows.

[Paul_Hossler]

Yes

rows("10:20").hidden=true

or

rows(30).resize(15).hidden = true

[Ladyj205]

Hi Paul,

Everytime i insert a new row. my table with optionbuttons change and it places the value elsewhere.

is there a way i can have my table to look like this when there are new rows insert. im still pulling hair but not as much.