Articles

Macro to fill down based on condition.

December 12, 2010 at 11:13:51
Specs: Windows 7

Hi,

I need a macro that can fill-down based on following criteria. The spreadsheet I have contain data ranging from A1 to C200, every cells from B1 to C200 contain data except the EMPTY ROW that comes after 2 or 3 or 4 or 5 rows ( it varies) While in column A there are many blank cells (Of course it includes aforementioned EMPTY ROW). What I need is the content of a PARTICULAR cell in column A is to be filled in every cells until one cell above of the EMPTY ROW. (EMPTY ROW SHOULD NOT BE FILLED OR DELETED, IT SHOULD BE LEFT AS IT IS). Again we will start fill-down from the next cell after the EMPTY row until one cell above the NEXT EMPTY ROW. It continues until 2 blank ROWS comes together.

REMEMBER, The data in cell after every EMPTY ROW are different.

Thanks in advance.


See More: Macro to fill down based on condition.

Report •


#1
December 12, 2010 at 17:14:54

re: What I need is the content of a PARTICULAR cell in column A is to be filled in every cells until one cell above of the EMPTY ROW.

Please refrain from using all capital letters in your posts. It doesn't make your post any clearer or easier to understand. In fact, it's more annoying than helpful.

What do you mean by "the content of a particular cell in column A"?

Particular means specific or singular. What specific or singular cell are you referring to.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 12, 2010 at 22:37:09

Hi Derby

First of all thanks for your advice, I know that using capital letter while having conversation is not good at all. Here, I used capital letter because I wanted the reader to note them. Anyway, here is the sample.

Column A Column B Column C
R1 123456 ABCD XYYOL
R2
R3 112233 QWER QWSDI
R4 DERFET
R5 GRTFED
R6 VFDCIP
R7 SDCXOI
R8
R9 111222 SXDCFV DFD362D
R10 DAFAD25
R11 DEREDJK
R12 SDF365DI
R13 SFDGFGF
R14
R15 879465 AS25DFE GHTY654I
R16 SDFDF458
R17 ERE8759LI
R18 WEREFDSF
R19 DSF2215O
R20
R21 234546 DMFCRYR 124680808

Please note that R2,R8,R14, and R20 are blank rows and nothing should be filled in. Now, what I want is the data of A3:B3 fill all the way down to A7:B7 (since row 8 (R8) is blank it should leave as it is). Then we will start from next row after the blank row. That is R9, here data of A9:B9 will filled down all the way down to A13:B13 (Again A14 is not filled since its blank). we will continue this steps until we meet 2 or more blank rows, Which means the table is finished.

Data in A1:B1 would not be filled down since next row is blank.

Hope its more clear.


Report •

#3
December 13, 2010 at 04:12:53

I suspect that your data is not lined up correctly. Please click on the words How To in my signature line to view instructions on how to post a table in this forum.

Regarding the use of upper case, this forum offers bold and italic formatting for emphasis. Note the icons above the Post Reply box.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
December 13, 2010 at 06:22:15

Hi,

    Column A   Column B    Column C
R1   123456	 ABCD       XYYOL
R2	
R3   112233      QWER      QWSDI
R4	                   DERFET
R5	                   GRTFED
R6	                   VFDCIP
R7	                   SDCXOI
R8	
R9   111222     SXDCFV     DFD362D
R10	                   DAFAD25
R11	                   DEREDJK
R12	                   SDF365DI
R13                        SFDGFGF
R14	
R15   879465     AS25DFE   GHTY654I
R16	                   SDFDF458
R17	                   
R18   124587     ALDKEP    WEREFDSF
R19	                   DSF2215O
R20	
R21   234546    DMFCRYR	   124680808

R1 to R21 indicates the row number.

Please note that R2,R8,R14, R17 and R20 are blank rows and nothing should be filled in. Now, what I want is the data of A3:B3 fill all the way down to A7:B7 (since row 8 (R8) is blank it should leave as it is). Then we will start from next row after the blank row. That is R9, here data of A9:B9 will filled down all the way down to A13:B13 (Again A14 is not filled since its blank). we will continue this steps until we meet 2 or more (more than one) adjacent blank rows, Which means, there is nothing more to be filled down

Data in A1:B1 would not be filled down since next row is blank.
Hope its more clear.

Thank you.


Report •

#5
December 13, 2010 at 06:58:33

Try this code in a backup copy of your workbook since macros can not be undone.

The code assumes your data resides in Sheet1. Adjust as required.

Option Explicit
Sub FillInData()
Dim lstRw, rw As Integer
 With Sheets(1)
'Determine Last Row In Column C (end of data)
  lstRw = .Range("C" & Rows.Count).End(xlUp).Row
'Loop through data
   For rw = 1 To lstRw
'If the next row is empty just increment Row counter
    If .Range("C" & rw + 1) = "" Then
     rw = rw + 1
     GoTo rwBlank
'If the next row isn't empty, copy data from
'Column A and B to row
    Else
      .Range("A" & rw & ":B" & rw).Copy _
       Destination:=.Range("A" & rw + 1)
    End If
rwBlank:
   Next
 End With
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#6
December 13, 2010 at 07:32:41

Thanks for your reply.

Tomorrow I will try this macro on my computer at office since I now use office 2010 Starter in my laptop that doesn't have macro or any developer level stuff. And am really disappointed too because of lacking of developer menu in excel 2010 Starter. By the way, Any suggestion for that?

Anyway I will try this macro as soon as I get my office and let you know.


Report •

#7
December 13, 2010 at 21:00:58

Marvellous!!!

Went without any mistakes. I will get back to you for some more information. Thank you once again.


Report •

#8
December 13, 2010 at 23:32:07

Your macro worked fine, There is one more column I need to fill down. Conditions, sheet and everything remain same. Apart from Column A and Column B I need Column I to be filled down. I tried to change the code, What happened is that all columns from A to I are filled. I only need Column A, Column B, and Column I to be filled down.


Report •

#9
December 14, 2010 at 04:28:11

One more thing need correction.

Row 3 contain headings, when I run macro what happened is that A3 and B3 are copied in A4 and B4. So, this process should start from 4th row onwards.

Thanks.


Report •

#10
December 14, 2010 at 05:08:34

Why didn't you tell me all of these requirements before?

Let's pretend you are a Project Manager for a major corporation and you gave your vendors half of your requirements and then sent them off to devise a solution. How much more do you think they would charge you to re-do the solution after you told them "Thanks, now add this, and this and this."?

It should be no different when you are getting free help. We have to set up spreadsheets, write code, test it, etc. We don't save every project we work on and when you come back with extra requirements we have to set up everything up again, test it all and re-do a lot of work.

If I have time, I'll consider working on your additional requirements.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#11
December 14, 2010 at 06:20:39

Well, thanks for the help you offered me.

First of all, I wanted to make my question as short as possible. As for Column I, I didn't mention that in the question because I didn't want to make it more complicated and I thought I could do it myself by changing some codes, now I knew its beyond my ken.

Secondly, I didn't know that any help I get from this site is like the way you explained (Let's pretend you are a Project Manager for a major corporation and you gave your vendors half of your...). I actually thought that its some volunteers helping people

Anyway, Thanks for your time and help.


Report •

#12
December 14, 2010 at 07:45:12

Please see my response in your other thread.

In both instances, code was provided to meet your original requirements and then you changed them after a solution was offered.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •


Ask Question