Report

Copy range data in a column to new column. [Solved]

Ask a question anthonyishere 2Posts Tuesday April 19, 2016Registration date April 19, 2016 Last seen - Latest answer on Apr 21, 2016 11:49PM
Need help here please !!!
Data available : A range of data in a column. Some of the cells are blank.
My requirement : Is there any formula wherein I can copy data from the range in the column to another column in another sheet. I need to copy only filled cells. I also need to have a command which will automatically copy data by the click of one button maybe!! I tried a macro but it doesn't work.
See more 
Helpful
+0
moins plus
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.
anthonyishere 2Posts Tuesday April 19, 2016Registration date April 19, 2016 Last seen - Apr 19, 2016 04:56AM
Hi Zohaib,

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
Reply
Zohaib R 2403Posts Sunday September 23, 2012Registration date June 16, 2016 Last seen - Apr 19, 2016 10:35AM
Try this! Hit Alt + F11 and you will get code for your Macro. Alternatively, you can go to View > Macros > Edit, and this will also give you the code.
Reply
anthonyishere- Apr 21, 2016 03:43AM
Hi Zohaib,

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
Reply
Zohaib R 2403Posts Sunday September 23, 2012Registration date ModeratorStatus June 16, 2016 Last seen - Apr 21, 2016 10:52PM
Hi Anthony,

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.
Reply
anthonyishere- Apr 21, 2016 11:49PM
Hi Zohaib,

That does help.

Thanks a lot ! ! !

Regards

Anthony D
Reply
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!