How Can I Delete Cells In The Middle?

[charllie]

Hi Folks,

I have a worksheet which has a header and some headings in row 1 etc.

I also have in rows 4, 5 and 6 some headings which acuumalate figures.

I then use the following code which is activated by another userform commandbutton which inserts a row into row 2 and then inserts information into the columns. In short, it copies information inserts a new row in row 2 and then pastes the information.

I am then left with my headings my information which was copied across and now my heading which were in rows 4, 5 and 6 have now moved down a row. I hope this explains things clearly.

My problem is that later in the process this sheet needs to be cleared so we can start fresh. However i only need to clear the parts which contain the information that was copied across. Not the headings on the top and bottom. In short the information in the middle.

Can anyone advice me how to do this please.


This is the code i am using to copy and paste the information:

'This section transfers the new information entered to Shift Managers Daily Shift Report
            Dim DSR As Worksheet
                Set DSR = Workbooks("Shift Manager.xls").Worksheets("Daily Shift Report")
                    
                    DSR.Rows("2:2").Insert Shift:=Down 'Insert Row
                    
                    DSR.Range("A2") = Me.DateTextBox
                    DSR.Range("B2") = Me.ShiftTextBox
                    DSR.Range("D2") = Me.OrderNoTextBox
                    DSR.Range("E2") = Me.CatalogueTextBox
                    DSR.Range("F2") = Me.ConfigurationComboBox
                    DSR.Range("G2") = Me.CasHrsTextBox
                    DSR.Range("H2") = Me.TempHrsTextBox
                    DSR.Range("I2") = Me.PermHrsTextBox
                    DSR.Range("J2") = Me.TotalHrsTextBox
                    DSR.Range("K2") = Me.TotalDiscsTextBox
                    DSR.Range("C2") = Workbooks("Team Leader.xls").Worksheets("Team Leader Screen").Range("F6") 'Shift Pattern

Any help would be appreciated.

Thanks

 

[al_b_cnu]

hi Mike, how's it going?

You might bre able to adapt this code which deletes rows 2 to [End-Down]-1

Sub DeleteRows()
Dim WS As Worksheet

Set WS = Sheets("Sheet1")
WS.Rows("2:" & WS.Range("A2").End(xlDown).Row - 1).Delete shift:=xlUp
End Sub

 

[charllie]

Hi Alan,

Im fine thanks, had a week off so feeling good, lol.

Hope you are well.

Thanks for the code i will give it a go and let you know.

Thanks

 

[charllie]

Hi Alan,

That worked brilliantly, thank you.

In this part:

I also have in rows 4, 5 and 6 some headings which acuumalate figures.

It has now stopped adding up. I know there is probably an easy answer but how do i fis it so that for example G4 = the amount in B2. But when the next line is added G4 becomes H4. I then want to add the new figure in B2 to the running total in now H4.

Is this possible?

Mike

 

[al_b_cnu]

Hi Mike,

Try =Indirect("B2")

 

[charllie]

Hi Alan,

I =Indirect("B2") in cell G4 and it works to a fashion.

But I want it to keep adding (as a running total) all the figures that go in B2 each time a new row is added.

Is that possible?

Mike