AUTO FILL

mrbeanyuk

Board Regular
Joined
Nov 30, 2005
Messages
212
Hello

Could be tricky but here it goes.....

Column A has data in row 1, then blank cells to row 300, then blank cells to 350 then blank to 370 etc - basically data in the odd cell for about 4000 rows.

I would like to know if its possible to write a bit of coding I can link to a button that I can click when I dump new data in that reads Column A and finds the first cell with data and copies it to the blank cells until it reaches the next cell with data and copies that cell in the next lot of blanks until it gets to the next. (i,e copy A:1 to A:2 - A:300 then copy A:301 to A:302 - A:370)

Appreciate any ideas?

thanks
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
Sounds like you just want to delete the blank rows... you could either use code or you could filter (data --> filter --> autofilter) column A for either (1) blanks and delete the rows in the filtered results or (2) non-blanks and copy/paste the results.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Code:
Sub Test()
Application.ScreenUpdating = False
For i = 2 To 4000
     If Cells(i, "A").Value = "" Then
            Cells(i, "A").Value = Cells(i - 1, "A").Value
     End If
Next i
Application.ScreenUpdating = True
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Do you really need code?
  1. Select the column with the blanks.
  2. Goto Edit>Goto...Special and select blanks.
  3. Goto the formula bar, enter =A1 (or whatever the first cell with data is) and then press CTRL+ENTER.
  4. Select the column, copy and paste special values.
 

mrbeanyuk

Board Regular
Joined
Nov 30, 2005
Messages
212

ADVERTISEMENT

Do you really need code?
  1. Select the column with the blanks.
  2. Goto Edit>Goto...Special and select blanks.
  3. Goto the formula bar, enter =A1 (or whatever the first cell with data is) and then press CTRL+ENTER.
  4. Select the column, copy and paste special values.

I need code because i need it to copy cell a1 only in the blank cells down to the next cell where there is currently text and then copy that cell down etc.

Unfortunatly, i need to keep the blank cells in, because in column B and C there are no blanks.
 

mrbeanyuk

Board Regular
Joined
Nov 30, 2005
Messages
212
Code:
Sub Test()
Application.ScreenUpdating = False
For i = 2 To 4000
     If Cells(i, "A").Value = "" Then
            Cells(i, "A").Value = Cells(i - 1, "A").Value
     End If
Next i
Application.ScreenUpdating = True
End Sub

PERFECT!!!! Thanks very very very very much!!!!!!
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I need code because i need it to copy cell a1 only in the blank cells down to the next cell where there is currently text and then copy that cell down etc.

Norie's suggestion will accomplish EXACTLY the same thing my code does.

Try it...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

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
Top