Excel Macro help

  Boluwd 19:54 08 Jul 2008
Locked

I have tried to use the "record macro" tool in Excel to create an "insert new line" macro assigned to a simple button.
I have data and formulae in range B9:J9. I am attempting to copy this range without the data but retaining any formulae, into range B10:J10. I have hidden a copy of the formulae in range B9:J9 and have recorded the macro to firstly insert a row starting at B10 and then to copy and paste the contents of the hidden row into B10.By clicking the assigned button, the new row is created. But the next time I click the button the row is inserted again at row 10 instead of row 11.
I could just create a spreadsheet by dragging the entire range down as far as I want. But then the sheet will have a series of rows with #DIV/0! in the cells awaiting data. It would be much neater to be able to insert a fresh new row clear of data underneath the last one.

  VoG II 20:07 08 Jul 2008

In your example, is B10 the last filled cell in column B or are there data/formulas beneath it?

  VoG II 20:21 08 Jul 2008

Assuming that it is then perhaps the following will do what you want:


Sub cpy()
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("B" & LR & ":J" & LR).Copy Destination:=Range("B" & LR + 1)
Application.CutCopyMode = False
End Sub


However, there is another approach to this. Say you have a formula

=A1/B1

this will return #DIV0 if B1 is blank. You can get around this by using

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1/B1)

which will only show the results of the division if both A1 and B1 have values. You can drag this formula down as far as needed and it won't show any errors as the result of blank cells.

  Boluwd 20:22 08 Jul 2008

Hi VoG™, I was hoping you would appear!

B9 is the last filled cell in col B, until the macro copies the range starting at cell B10. Everything underneath row 10 is blank. What I then require from the macro is to repeat the copy/paste but into B11, then B12 etc.

  VoG II 20:25 08 Jul 2008

In that case the macro I posted will do what you asked. However, see my comments about suppressing error values due to blank cells.

  Boluwd 21:09 08 Jul 2008

Thanks for this VoG™. The script does copy and paste range B9:J9 to the next row down starting at B10, but it also copies the data in B9:J9. I need the range B10:J10 to be free of data and only have the formulae. (New data will be entered in some of the cells in row 10).
I have also realised that row 10 needs to be slightly different from row 9. The formula in H9 is (G9-D5) and for H10 it is (G10-G9), then H11 onwards will be (H11-H10....H12-H11 etc.
So what I really need is the script to copy only the formulae starting from row 10 to the next row down as before.
Apologies for my earlier misinformation! I need to be away from the PC now and I will login later this evening.
Thankyou for your assistance.

  Boluwd 21:11 08 Jul 2008

Small amendment required:
Thanks for this VoG™. The script does copy and paste range B9:J9 to the next row down starting at B10, but it also copies the data in B9:J9. I need the range B10:J10 to be free of data and only have the formulae. (New data will be entered in some of the cells in row 10).
I have also realised that row 10 needs to be slightly different from row 9. The formula in H9 is (G9-D5) and for H10 it is (G10-G9), then H11 onwards will be (G11-G10....G12-G11 etc.
So what I really need is the script to copy only the formulae starting from row 10 to the next row down as before.
Apologies for my earlier misinformation! I need to be away from the PC now and I will login later this evening.
Thankyou for your assistance.

  VoG II 21:32 08 Jul 2008

Try


Sub FILLDwn()
Dim LR As Long, NR As Long, i As Byte
LR = Range("B" & Rows.Count).End(xlUp).Row
NR = LR + 1
For i = 2 To 10
If Cells(LR, i).HasFormula = True Then
Cells(LR, i).AutoFill Destination:=Range(Cells(LR, i), Cells(NR, i)), Type:=xlFillDefault
End If
Next i
End Sub

  Boluwd 22:46 09 Jul 2008

I have messed around with this most of the evening and I've just discovered that as long as there is data in any of the cells in the last row, then the script does indeed create a blank new row. I had been using the script to create the new row, then repeating the script to create the next row without entering data in the preceding one!
This VBA stuff is all new to me! Can you recommend a Dumbshite Guide to this stuff as I often want to do something automated in Excel but I've always tried to use the record Macro tool.
I'm very grateful VoG™, thanks a lot.

  Boluwd 13:47 10 Jul 2008

Bumped as resolved.

  VoG II 14:26 10 Jul 2008

Glad it worked.

If you want a guide that assumes that you know nothing, takes you through the basics then onto stuff like UserForms then click here is excellent IMHO.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

InVision Studio takes on Adobe XD and Sketch

Camera tips to take better iPhone photos

Comment transformer un iPhone en borne Wi-Fi ?