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

G

gcaloyloy

New Member
Joined
Nov 27, 2018
Messages
4
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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]113[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]114[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]116[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]117[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]137[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]138[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]139[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEF345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]301[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]302[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]303[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEF345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEF345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]314[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEF345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]315[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]316[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](last row)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
gcaloyloy said:
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)
Click to expand...
So, you want to overwrite the existing data? What if D117 is blank?
 
Upvote 0
BEFORE:


[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]..Z[/TD]
[/TR]
[TR]
[TD]113[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK ROW/CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]114[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](ALWAYS BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]116[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](ALWAYS BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]117[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]137[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]138[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](ALWAYS BLANK ROW)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]139[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](ALWAYS BLANK ROW)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK ROW/CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEF345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]301[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](ALWAYS BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]302[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](ALWAYS BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]303[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]314[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]315[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK ROW/CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]316[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK ROW/CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](last row)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


AFTER:

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]..Z[/TD]
[/TR]
[TR]
[TD]113[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK ROW/CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]114[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]115[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](ALWAYS BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]116[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](ALWAYS BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]117[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]137[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]138[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](ALWAYS BLANK ROW)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]139[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](ALWAYS BLANK ROW)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK ROW/CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEF345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]301[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](ALWAYS BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]302[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](ALWAYS BLANK CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]303[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEF345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEF345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]314[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DEF345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]315[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK ROW/CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]316[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](BLANK ROW/CELL)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]xxx[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](last row)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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).
 
Last edited:
Upvote 0
gcaloyloy said:
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)
Click to expand...
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?
 
Last edited:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Find 1st instance in column A, paste corresponding value from column B into another cell and continue down until end of data
Replies
2
Views
83
MrsFraser07
M
J
  • Solved
How to sum values in column D until a blank cell is reached and if values in column C equals "WP" or "BS"
Replies
9
Views
2K
Peter_SSs
Peter_SSs
H
  • Solved
VBA copy-paste down column, without stopping at non-blank cells.
Replies
3
Views
616
Tetra201
Tetra201
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
399
willow1985
willow1985
G
  • Question Question
Formula to look upwards in column and return value below first blank cell encountered
Replies
8
Views
3K
GTS
G

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top