How to sort data in active row using VBA in Excel 2010.

February 19, 2014 at 02:00:10
Specs: Windows 7
Hi

My problem is as follows.

I have many rows of data and the data is stored in columns A:M.

Some of the cells in a particular row needs to be deleted or moved into an adjacent columns in the same row. It is always the same cells that are affected, this is due to data being copied from another excel spreadsheet with columns in different order.

To make the space i have copied all the cells to column N:Y in the same row on mouse click, clearing all cells A:M.

I have tried to use a macro to move the cells into their new position but i keep getting errors.

Can you give me some pointers, on how i should solve this problem,

Thank you.


See More: How to sort data in active row using VBA in Excel 2010.

Report •


#1
February 19, 2014 at 05:47:51
Can you tell us what errors you are getting and also show us the code you are using, this will help us find where the errors are.

Report •

#2
February 19, 2014 at 08:37:54
Hi thank you for quick response, since posting i have come up with a partial solution but the code still isnt working correctly. I am new to VBA and searching the web for codes that to me appear logical (probably wrong ) and then trying to impliment.

if i fill two rows, row two is somehow getting affected and i am not sure why.

Appreciate the help, here is the code i have badly crafted.


'copy cells in active row
copyRange = Range("$b" & ActiveCell.Row & _
":$m" & ActiveCell.Row).Address
'Copy/PasteSpecial Values
Range(copyRange).Copy

'pick target & paste
Range("$n" & ActiveCell.Row).PasteSpecial Paste:=xlPasteValues


'delete info nolonger required
Range("$B" & ActiveCell.Row & _
":$m" & ActiveCell.Row).Delete

'copy cells in active row
copyRange = Range("$r" & ActiveCell.Row).Address

Range(copyRange).Copy

Range("$b" & ActiveCell.Row).PasteSpecial Paste:=xlPasteValues

copyRange = Range("$Q" & ActiveCell.Row).Address

Range(copyRange).Copy

Range("$G" & ActiveCell.Row).PasteSpecial Paste:=xlPasteValues


copyRange = Range("$P" & ActiveCell.Row).Address

Range(copyRange).Copy

Range("$C" & ActiveCell.Row).PasteSpecial Paste:=xlPasteValues


copyRange = Range("$U" & ActiveCell.Row).Address

Range(copyRange).Copy

Range("$H" & ActiveCell.Row).PasteSpecial Paste:=xlPasteValues

'delete info nolonger required
Range("$N" & ActiveCell.Row & _
":$U" & ActiveCell.Row).Delete


Application.CutCopyMode = False


Report •
Related Solutions


Ask Question