Enter Information in next blank link (even if some columns have formulas

**kgreave1** · 06-22-2012, 06:50 PM

Okay, so I am trying to set this User Form to autofill the next blank line. It should, and does, fill the information into columns A:D. However, I have formulas in columns E:L and when I fill those formula's down (so that they will autofill as new information is added through the form) the macro forces the information to the bottom of the rows (the first row with no formula's filled down in E:L)

How can I change this to have it fill based on the blanks only being found in A:D (and ignore the formula's in E:L)

Someone fix this to insert the info in the next blank line between A:D (formulas are in E and on, so its skipping to the bottom of where the formulas are)

Problem Coding:

Please Login or Register  to view this content.

Entire Code:

Please Login or Register  to view this content.

edit: I would post the file, but due to security reasons, I am not really supposed to.

**JS411** · 06-23-2012, 12:54 AM

Try replacing this line...

Please Login or Register  to view this content.

...with this...

Please Login or Register  to view this content.

**kgreave1** · 06-24-2012, 01:16 AM

Originally Posted by JS411

Try replacing this line...

Please Login or Register  to view this content.

...with this...

Please Login or Register  to view this content.

That seems to do the trick! I will have to wait until monday to test it fully, but I believe that will work. Thanks!

**kgreave1** · 06-25-2012, 05:36 PM

Worked like a charm. I do have one additional question that you may be able to help with. I was hoping to have it copy down the formatting from above, specifically the boarders. I have everything with information set up to show all boarders. I know when you use the Excel Data Form, it will copy those boards down to a new row. Can this be done in a User Form?

**JS411** · 06-27-2012, 02:51 AM

If the formatting (including borders) will be the same as the row above where the new data is being entered, then you can copy the previous row and PasteSpecial(xlPasteFormats) into the new row.
You'll have to consider how this would be handled for the first row of data if you have a header row with different formatting.

The other option would be to add the borders to the new row of data. You can record a macro to get the syntax if you decide to go that route.