Solved: Copy content of upper cells

[danovkos]

Hi all,
pls. how can i do the following:
I want to copy in selected range of cells (i select the range) content of cells over the selected cells.
I know, that there are something like ctrl+d, but i need it i little bit more complicatet. I want to coppied in selected range but there can be used a filter. So there can be hiddne cells and.
If i use ctrl+d it doesnt works if is filter used. It Works only for single cell.
What for code can i use?
thx

[danovkos]

for better specification
i want to use it with filter, but macro supposed to copy real upper cell
i mena,
i have cells
a1,2,3
if is row 1,2 hidden and 3 is free, after then i run the macro on cell A3, it will copy content of A2 in spite of the A2 is hidden

[Aussiebear]

Would you post a workbook to show the data layout you are using?

[danovkos]

yes of course,
i use it with this way.
I filter blank (f.e. in column AK) and then i want to copy the upper value)

reason: i have clients and they have accounts, One client can have one or more rows (accounts). The data in cells (f.e. identification data) are the same for all clients rows. Sometimes i add a new rows (one or more) for clients and i need to add existing data. I always add a new row at the end of client acounts. If is client first row on row 100 and your acc. are on the rows 100,101,102, i add a other with insert rows and it is on row 103.

[Mr.G]

I am trying to understand.......you want to search for the first empty cell down a column and then copy the value above the empty cell?

[danovkos]

no,
i want to the same function as ctrl+D do (fill down), but i need it for the range, which i select (more cells) and i want to use it during filtering data. So i use it when data are hidden. But the function is the same - copy content of cell to selected, from cell over. If i select A3, i want to copy content from A2 (also if A2 is hidden/with filter) and this for all cells, which are selected in range.

[Mr.G]

I am stumped

[danovkos]

OK
i try with other words
i have a huge table of data 6000 rows and 100 col.
In this table are some cells blank. So i run autofilter and look at one of many columns. I choose one and filtering only blank cells, but it will be blank also other column too. Then i select all blank filtered cells and here comes macro. I want after running macro filling the blank cells with conten, which are in cell over each cells.
I will always sellecting only blank, because there i need to fill data which are in cells about one row higher in the same column.
Simply result will be, that in blank cells will by after runing macro conten from the cell above.
I know, that my english is bad.....i do my best

[Mr.G]

No.....sorry to you for that. I just don't think I have your answer....but....

I have this to select the cell above your selected cell....

[VBA]
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
ActiveCell.Offset(-1, 0).Select
End Sub
[/VBA]

Just right click your mouse and it selects above cell.

[danovkos]

i dont understund, what this code supposed to do
i dont need this
but thx

[danovkos]

maybe can anyone fix this code what i get togehter..but doesnt work.
i want to use command

Selection.FillDown

for all cells in range, but the source for this cells are cells which are invisible/hidden by filter...
is it imposible?
thx

Sub fill3()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range.SpecialCells(xlCellTypeVisible)
    Selection.FillDown
Next cell
Application.ScreenUpdating = True
End Sub

[mdmackillop]

This will fill all blanks in the active cell column. Is this what you are after?
[vba]
Sub FillDownSelected()
Dim Limit As Long
Dim Col As Long
Dim Cel As Range
Limit = 1581
Col = ActiveCell.Column
For Each Cel In Range(Cells(4, Col), Cells(Limit, Col))
If Cel = "" Then
Cel.Value = Cel.Offset(-1).Value
Cel.Interior.ColorIndex = 22
End If
Next
End Sub

[/vba]

[danovkos]

yes this works, but
- only for one column (mostly i select area which can be f.e.c5:w150)
- i don know why, it fill content to row 1581 (but i need fill only selected area) - i see it in code but i dont now what is the reason of this limit

but this is big step closer to my idea..

[mdmackillop]

1581 was the limit as your data seemed to change there.
I'm still not 100% sure but give this a try
[vba]
Sub FillDownSelected()
Dim Cel As Range
For Each Cel In Selection
If Cel = "" Then
Cel.Value = Cel.Offset(-1).Value
Cel.Interior.ColorIndex = 22
End If
Next
End Sub
[/vba]

[danovkos]

mdmackillop you are fantastic

this code is better as i wish...it works with or without filter and also it looks if i select blank or nonblank and fill content only in blank, and fill color to filled cells..

simply fantastic ...
really thank you very much

you are one of the best on this forum, for me sure
thx