Copy visible cells from Row B paste to Row A in filter mode

[lakshmipathi123]

Hi Experts,

I have query regarding visible cells macro. I need copy the formula from A20 to used range from B and paste visible cells from Row A20. Could you please help me regarding this. I have attached below codings.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Liability[/TD]
[TD]Supplier Name
[/TD]
[/TR]
[TR]
[TD]=TRIM(B2)[/TD]
[TD]Liability Account :20110-2900-009669-0007-00000000[/TD]
[/TR]
[TR]
[TD]=TRIM(B3)[/TD]
[TD]Liability Account :20280-0000-008053-0007-00000000[/TD]
[/TR]
</tbody>[/TABLE]

'Getting value from range B to A using Trim
Range("B20").Select
Selection.Copy
Range("A20").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[1])"
Could you please provide codings copy the formula from A20 to used range from B and Paste visible cells from A20 to till Range "B" data availability?
Range("B20").Select
Selection.End(xlDown).Select
Range("A15844").Select ' last row should find based B data availability (it should not stop A15844 data may increase or decrease)
Range(Selection, Selection.End(xlUp)).Select
Range("A20:A15844").Select
Range("A15844").Activate
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFilter
Columns("A:A").Select
Range("A15512").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Thanks in advance
Lakshmipathi

 

[JLGWhiz]

I believe this is what you want.

From:

Code:
Selection.Copy
Code:

To:

Code:
Selection.SpecialCells(xlCellTypeVisible).Copy
Code:

 

[lakshmipathi123]

Hi,

The above code is not clear to me....can u plz explain in detail

Thank you,
Lakshmipathi

 

[chetanmehra1989]

your a colum should be blank after tht u can run this code

Range("a1").Select
''Cells(2, 3).Select


Do Until Selection.Offset(0, 1).Value = ""
Selection.Value = "=trim(rc[1])"
Selection.Offset(1, 0).Select


Loop

 

[lakshmipathi123]

Hi Chetanmehra

The above codings are not selecting visible cells. Its working only for range row 1 and 2. Grateful if you could provide me coding based on first email

Thanks for your understanding
Lakshmipathi

 

[lakshmipathi123]

Hello All,

I got the answer myself...below the code

'Put filter in Range B
Application.StatusBar = "Running on Liability..."
With ActiveSheet.Range("B20:B" & ActiveCell.SpecialCells(xlCellTypeLastCell).Row)
.AutoFilter Field:=1, Criteria1:="=Liability*"
End With

'Trim Liability, copy and paste visible cells
With Range("A20:A" & Range("B" & Rows.Count).End(xlUp).Row)
.Offset(1, 0).FormulaR1C1 = "=TRIM(RC[1])"
With ActiveSheet.Range("B19:B" & ActiveCell.SpecialCells(xlCellTypeLastCell).Row)
.AutoFilter Field:=1
End With
.Value = .Value
End With

Thanks for your time
Lakshmipathi