Find first cell in column with content and copy down. (more)

[gcaloyloy]

Hello,

I am a new user here, thank you in advance for helping me. As well, I have basic VBA skills and really trying to wrap my head around this problem...

This is what I would like to do.

1) I would like to scan from the top of column "D"
2) As it is scanning down the cells in column "D" it reaches its first data (eg. ABC123, in cell D114)
3) I would like to skip the next two rows D115 & D116 (information is not important)
4) Starting at D117, write the data it found in D114 (eg. ABC123) to the cells below it until it reaches its first BLANK ROW (eg. D138 is blank, D117-D137 would have the data ABC123)
5) Continue down the rest of column "D" until it reaches the second data (eg. DEF456)... repeat steps 3 & 4 respectively for the new data.
6) The code will stop when it reaches the bottom of the sheet containing this data.

	A	B	C	D	E	F	G	H	I	J	K
113				(BLANK)
114				ABC123
115				(BLANK)
116				(BLANK)
117				ABC123
...				ABC123
137				ABC123
138				(BLANK)
139				(BLANK)
...				(BLANK)
300				DEF345
301				(BLANK)
302				(BLANK)
303				DEF345
...				DEF345
314				DEF345
315				(BLANK)
316				(BLANK)
xxx				(last row)

<tbody>
</tbody>

Thanks

 

[yky]

4) Starting at D117, write the data it found in D114 (eg. ABC123) to the cells below it until it reaches its first BLANK ROW (eg. D138 is blank, D117-D137 would have the data ABC123)

So, you want to overwrite the existing data? What if D117 is blank?

 

[gcaloyloy]

D117-D137 cells will have D114's information. Initially the cells will have nothing in it .

 

[gcaloyloy]

BEFORE:

	A	B	C	D	E	F	..Z
113				(BLANK ROW/CELL)
114				ABC123
115				(ALWAYS BLANK CELL)
116				(ALWAYS BLANK CELL)
117				(BLANK CELL)
...				(BLANK CELL)
137				(BLANK CELL)
138				(ALWAYS BLANK ROW)
139				(ALWAYS BLANK ROW)
...				(BLANK ROW/CELL)
300				DEF345
301				(ALWAYS BLANK CELL)
302				(ALWAYS BLANK CELL)
303				(BLANK CELL)
...				(BLANK CELL)
314				(BLANK CELL)
315				(BLANK ROW/CELL)
316				(BLANK ROW/CELL)
xxx				(last row)

<tbody>
</tbody>

AFTER:

	A	B	C	D	E	F	..Z
113				(BLANK ROW/CELL)
114				ABC123
115				(ALWAYS BLANK CELL)
116				(ALWAYS BLANK CELL)
117				ABC123
...				ABC123
137				ABC123
138				(ALWAYS BLANK ROW)
139				(ALWAYS BLANK ROW)
...				(BLANK ROW/CELL)
300				DEF345
301				(ALWAYS BLANK CELL)
302				(ALWAYS BLANK CELL)
303				DEF345
...				DEF345
314				DEF345
315				(BLANK ROW/CELL)
316				(BLANK ROW/CELL)
xxx				(last row)

<tbody>
</tbody>

 

[Rick Rothstein]

Your picture is kind of unclear... what distinguishes a blank cell from a blank row? Is there other data in other rows somewhere? If so, how many columns can possibly contain data?


A Generalized "Please Note"
--------------------------------------
For future questions you may ask, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[yky]

4) Starting at D117, write the data it found in D114 (eg. ABC123) to the cells below it until it reaches its first BLANK ROW (eg. D138 is blank, D117-D137 would have the data ABC123)

The example you gave doesn't differentiate "blank cell" and "blank row". D117 to D137 are all blank. Why does writing stop at D138, and not D118 or anywhere between D117 and D137?

 

[gcaloyloy]

Ok I'll put the code here for you to look at. I'll try to explain what I am trying to do as I have basic VBA knowledge.
I apologize for the confusing spreadsheet...
I have a button to run the VBA code below on some information I've pasted into a spreadsheet.


Private Sub CommandButton1_Click()

' I have information in Column G that has some spaces to trim
Dim Addr As String
Addr = "G1:G" & Cells(Rows.Count, "G").End(xlUp).Row
Range(Addr) = Evaluate("IF(" & Addr & "="""","""",TRIM(" & Addr & "))")



' I ran the record macro here and filtered what I didn't want and delete the entire row
Columns("G:G").Select
Selection.AutoFilter
ActiveSheet.Range("$G$1:$G$2000").AutoFilter Field:=1, Criteria1:=Array( _
"DC N", "DC P", "N", "Y AC N", "Y DC N"), Operator:=xlFilterValues
ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)

`Turning off the autofilter
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If

`This is where I would like to check all of column D
`I would like to start at column D, row 9 (I have some information from rows 1-8 I would like to keep - header info)
`In my spreadsheet, because of the filter I did prior to this there is no content from Cell D9-D113 << these rows 9-113 can be deleted
`Loop starts here checking for value in CELL D.
`At D114, I have a value in there which spans all of row 114 from column D-Q << I need to keep this row
`Row 115 is blank
`Row 116-137 has information in it which spans from column D-Q << I need to keep rows 114-137
`Rows 138-139 are empty << when there is TWO empty rows after the last row containing some values, start loop again.
`Loop starts again and continues on from row 140 to check for a value in CELL D.


Range("$D$1:$D$2000").Select
Do While Not (IsEmpty(ActiveCell))
ActiveCell.Offset(0, -1) = Range("$D$1").Value
ActiveCell.Offset(1, 0).Select
Loop



End Sub