Copy cell value above to cells below if blanks

**Fawkes_** · 02-10-2017, 01:14 PM

Hey Everyone,

There is an issue im running into here and I cant figure it out.

I have a program to take account numbers and separate them into single rows, once they populate into the new rows I want to get the blanks to fill with the information from the account in the rows adjacent to them. If I use the last row method the macro returns ref, but if I use a set number of rows this does not occur.

Here is my current code.

Please Login or Register  to view this content.

So it breaks column A into individual rows, and then scans for blanks, it should take the information from the cell above, and then copy that into the cell below for columns b-f (G-R) do not need to fill blanks at all they can be left as is.

The problem is the worksheet can change length so I tried to add the last row, now I get REF! instead of the name from the cell above on any instance where there are multiple splits

**Arkadi** · 02-10-2017, 02:22 PM

Hi fawkes... first:

Please Login or Register  to view this content.

is a bit redundant... lastrow has been calculated in the first, and then the calculation is done a second time in the second line. Just use (to replace the second line, leave the first):

Please Login or Register  to view this content.

Totally separate, lets for example assume that lastrow is 52 (random example). This line:

Please Login or Register  to view this content.

means you are putting together A2 and lastrow, so it is the same as saying Range("A252"). Maybe you meant Range("A2:A" & lastrow)? That would make the search for blanks in the range A2 to the last row in column A.
Or, if you are searching B to G, from row 2 to the last one then

Please Login or Register  to view this content.

**Fawkes_** · 02-10-2017, 03:24 PM

as always arkadi you are correct, and I missed some super simple things...

I am however getting an error on the last line where the remove duplicates takes place. it needs to go through column a and delete any rows that have duplicates. so if it is a duplicate acct number it should delete that row keeping only one of every account number.

Here is my adjusted code.

Please Login or Register  to view this content.

**Arkadi** · 02-10-2017, 03:43 PM

Range.Removeduplicates... the range is all the range (all columns and rows) that need to be scanned and removed, the columns argument is the column so find duplicates in. Since your range is only column A, there is no column 2, and thus the error. What you need (I believe) is:

Please Login or Register  to view this content.

**Fawkes_** · 02-10-2017, 03:52 PM

Im starting to scratch my head here, there are three sheets I use this on for one it works all the way down no problem and for two it completes the macro but it stop filling the balnk cells half way down... im confused. maybe the equation is the problem?

**Arkadi** · 02-10-2017, 05:40 PM

Not at a pc now.... Lemme get back to you...all have data all the way down colum a right? Last row is based on that column.

**Fawkes_** · 02-10-2017, 05:50 PM

Yes column a is 100% populated. problem is some people submit with the account numbers like this ######/ ######/ ###### so I wrote this to split them into their own rows. but now after switch to the lastrow methos so it would be able to handle any new additions, it is failing on the copy portion.

**Arkadi** · 02-10-2017, 06:31 PM

Oooh... Now i see... So at start u get last row but then you are making new rows.... After "next rindex" you need to re-calculate lastrow.... Just repeat lastrow=..... After your rindex loop finishes

**Arkadi** · 02-10-2017, 06:33 PM

Lastrow is set at start and unless u update it, it thinks lastrow is still the old last row

**Fawkes_** · 02-13-2017, 10:14 AM

That makes a lot of sense smh... Thank you again

**Arkadi** · 02-13-2017, 10:19 AM

You are welcome Fawkes_

I assume that resolved your issue?
Thanks for marking the thread as solved!

**Fawkes_** · 02-13-2017, 11:11 AM

Hey Arkadi, I had a question about that big project you helped me with, Is it alright if I inbox you?

**Arkadi** · 02-13-2017, 11:17 AM

yeah sure, go ahead

Copy cell value above to cells below if blanks

LinkBack

Thread Tools

Rate This Thread

Display

Copy cell value above to cells below if blanks

Re: Copy cell value above to cells below if blanks

Re: Copy cell value above to cells below if blanks

Re: Copy cell value above to cells below if blanks

Re: Copy cell value above to cells below if blanks

Re: Copy cell value above to cells below if blanks

Re: Copy cell value above to cells below if blanks

Re: Copy cell value above to cells below if blanks

Re: Copy cell value above to cells below if blanks

Re: Copy cell value above to cells below if blanks

Re: Copy cell value above to cells below if blanks

Re: Copy cell value above to cells below if blanks

Re: Copy cell value above to cells below if blanks

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find non-blanks cells and copy to another worksheet

[SOLVED] VBA Macros to Look for blanks cells and copy data

[SOLVED] Copy cells from one worksheet to another while skipping blanks

How to copy a formula to multiple cells without filling blanks cells too..

copy cells ignoring blanks

copy cells but miss blanks

Copy cells to adjacent column, removing blanks

Bookmarks

Bookmarks

Posting Permissions