AUTO FILL

[mrbeanyuk]

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

 

[Oaktree]

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]

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]

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]

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]

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]

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...