Copy range data in a column to new column.
Solved/Closed
anthonyishere
Posts
2
Registration date
Tuesday April 19, 2016
Status
Member
Last seen
April 19, 2016
-
Apr 19, 2016 at 12:13 AM
anthonyishere - Apr 21, 2016 at 11:49 PM
anthonyishere - Apr 21, 2016 at 11:49 PM
Related:
- Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as range names.
- Ideogram ai names - Guide
- My contacts list names - Guide
- Wow monk names - Guide
- Laptop keyboard symbol names - Guide
- How to count names in excel - Guide
1 response
Zohaib R
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
69
Apr 19, 2016 at 01:26 AM
Apr 19, 2016 at 01:26 AM
Hi anthonyishere,
I wish to help you on this. In my opinion a Macro will accomplish the required task. If you can share the Macro you have written, I can help you modify it to your use.
I wish to help you on this. In my opinion a Macro will accomplish the required task. If you can share the Macro you have written, I can help you modify it to your use.
Apr 19, 2016 at 04:56 AM
Thx for the reply. I didn't write any code for the macro. I used the wizard. I recorded copying the data n deleting all blanks. I know its very crude but am a mech engr n not a software guy.
Hence could you help me on this?
Thanks a lot
Anthony D
Apr 19, 2016 at 10:35 AM
Apr 21, 2016 at 03:43 AM
The code is:
Sub Blanks()
'
' Blanks Macro
'
'
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A24"), Type:=xlFillDefault
Range("A1:A24").Select
Rows("20:24").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:B1"), Type:=xlFillDefault
Range("A1:B1").Select
Selection.AutoFill Destination:=Range("A1:B19"), Type:=xlFillDefault
Range("A1:B19").Select
Rows("4:4").Select
Selection.Delete Shift:=xlUp
Rows("9:9").Select
Selection.Delete Shift:=xlUp
Rows("10:10").Select
Selection.Delete Shift:=xlUp
Range("14:14,11:11").Select
Range("A11").Activate
Selection.Delete Shift:=xlUp
Range("A2:A3").Select
Selection.AutoFill Destination:=Range("A2:A20"), Type:=xlFillDefault
Range("A2:A20").Select
Rows("20:20").Select
Selection.Delete Shift:=xlUp
Range("D6").Select
End Sub
----------------------------------
The initial table was as follows:
Sl Pr (kg/cm2)
1 62
2 43
3
4 61
5 47
6 80
7 42
8 84
9
10 83
11
12 44
13
14 82
15 58
16
17 61
18 81
------------
And required new table as follows:
Sl Pr (kg/cm2)
1 62
2 43
3 61
4 47
5 80
6 42
7 84
8 83
9 44
10 82
11 58
12 61
13 81
14
15
16
17
18
-----------------
Any help on this please ???
Thx
Apr 21, 2016 at 10:52 PM
Try this one. I just modified your Macro a little bit, this should work:
Sub Blanks()
'
' Blanks Macro
'
'
Columns("A:A").Select
Selection.Copy
ActiveSheet.Next.Select
Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Previous.Select
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("B1").Select
ActiveSheet.Range("$A$1:$C$19").AutoFilter Field:=2, Criteria1:="<>"
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
ActiveSheet.Next.Select
Range("B1").Select
ActiveSheet.Paste
End Sub
Please do write back to us.
Apr 21, 2016 at 11:49 PM
That does help.
Thanks a lot ! ! !
Regards
Anthony D