PDA

View Full Version : Solved: Copy content of upper cells



danovkos
05-29-2009, 12:35 AM
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
05-29-2009, 12:46 AM
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
05-29-2009, 12:46 AM
Would you post a workbook to show the data layout you are using?

danovkos
05-29-2009, 12:54 AM
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
05-29-2009, 04:19 AM
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
05-29-2009, 04:25 AM
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
05-29-2009, 04:43 AM
:dunno I am stumped

danovkos
05-29-2009, 05:12 AM
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
05-29-2009, 05:27 AM
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....


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


Just right click your mouse and it selects above cell.

danovkos
05-29-2009, 05:39 AM
:(
i dont understund, what this code supposed to do :(
i dont need this :(
but thx

danovkos
05-29-2009, 06:15 AM
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
05-29-2009, 08:26 AM
This will fill all blanks in the active cell column. Is this what you are after?

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

danovkos
05-31-2009, 10:54 PM
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
05-31-2009, 11:32 PM
1581 was the limit as your data seemed to change there.
I'm still not 100% sure but give this a try

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

danovkos
06-01-2009, 12:02 AM
mdmackillop you are fantastic:clap: :bow:

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:rotlaugh:

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