• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel formula to copy data in reverse order

K

Kg Teh

New Member
Hi All,

Can guide how to copy data in reverse order ?

Thank you very much !
 

Attachments

  • Question.xlsx
    11.6 KB · Views: 17
Hi Khalid, thanks for prompt feedback.

The formula works for number, what if the data i would like to copy is non-number, i.e. ABCDEFG ?

Thanks..
 
Hi,

The below code will do the work

Code: 
Public Sub Reverse_Rows_or_Columns()
'This Macro will reverse a selection of rows or columns.
'Note: you cannot select an etire row or column, but one
'cell less than that will work fine.
'Don't forget to assign this macro a keyboard shortcut or
'a toolbar button.

Dim Arr() As Variant
Dim rng As Range
Dim c As Range
Dim Rw As Long
Dim Cl As Long
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Set rng = Selection
Rw = Selection.Rows.Count
Cl = Selection.Columns.Count
If Rw > 1 And Cl > 1 Then
MsgBox "Must select either a range of rows or columns, but not simultaneaously columns and rows.", _
vbExclamation, "Reverse Rows or Columns"
Exit Sub
End If
If rng.Cells.Count = ActiveCell.EntireRow.Cells.Count Then
MsgBox "Can't select an entire row, only up to one cell less than an entire row.", vbExclamation, _
"Reverse Rows or Columns"
Exit Sub
End If
If rng.Cells.Count = ActiveCell.EntireColumn.Cells.Count Then
MsgBox "Can't select an entire column, only up to one cell less than an entire column.", vbExclamation, _
"Reverse Rows or Columns"
Exit Sub
End If
If Rw > 1 Then
ReDim Arr(Rw)
Else
ReDim Arr(Cl)
End If
Rw = 0
For Each c In rng
Arr(Rw) = c.Formula
Rw = Rw + 1
Next c
Rw = Rw - 1
For Each c In rng
c.Formula = Arr(Rw)
Rw = Rw - 1
Next c
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
 
Sathish KV said:
Hi,

The below code will do the work

Code: 
Public Sub Reverse_Rows_or_Columns()
'This Macro will reverse a selection of rows or columns.
'Note: you cannot select an etire row or column, but one
'cell less than that will work fine.
'Don't forget to assign this macro a keyboard shortcut or
'a toolbar button.

Dim Arr() As Variant
Dim rng As Range
Dim c As Range
Dim Rw As Long
Dim Cl As Long
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Set rng = Selection
Rw = Selection.Rows.Count
Cl = Selection.Columns.Count
If Rw > 1 And Cl > 1 Then
MsgBox "Must select either a range of rows or columns, but not simultaneaously columns and rows.", _
vbExclamation, "Reverse Rows or Columns"
Exit Sub
End If
If rng.Cells.Count = ActiveCell.EntireRow.Cells.Count Then
MsgBox "Can't select an entire row, only up to one cell less than an entire row.", vbExclamation, _
"Reverse Rows or Columns"
Exit Sub
End If
If rng.Cells.Count = ActiveCell.EntireColumn.Cells.Count Then
MsgBox "Can't select an entire column, only up to one cell less than an entire column.", vbExclamation, _
"Reverse Rows or Columns"
Exit Sub
End If
If Rw > 1 Then
ReDim Arr(Rw)
Else
ReDim Arr(Cl)
End If
Rw = 0
For Each c In rng
Arr(Rw) = c.Formula
Rw = Rw + 1
Next c
Rw = Rw - 1
For Each c In rng
c.Formula = Arr(Rw)
Rw = Rw - 1
Next c
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
Click to expand...
Sathish KV said:
Hi,

The below code will do the work

Code: 
Public Sub Reverse_Rows_or_Columns()
'This Macro will reverse a selection of rows or columns.
'Note: you cannot select an etire row or column, but one
'cell less than that will work fine.
'Don't forget to assign this macro a keyboard shortcut or
'a toolbar button.

Dim Arr() As Variant
Dim rng As Range
Dim c As Range
Dim Rw As Long
Dim Cl As Long
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Set rng = Selection
Rw = Selection.Rows.Count
Cl = Selection.Columns.Count
If Rw > 1 And Cl > 1 Then
MsgBox "Must select either a range of rows or columns, but not simultaneaously columns and rows.", _
vbExclamation, "Reverse Rows or Columns"
Exit Sub
End If
If rng.Cells.Count = ActiveCell.EntireRow.Cells.Count Then
MsgBox "Can't select an entire row, only up to one cell less than an entire row.", vbExclamation, _
"Reverse Rows or Columns"
Exit Sub
End If
If rng.Cells.Count = ActiveCell.EntireColumn.Cells.Count Then
MsgBox "Can't select an entire column, only up to one cell less than an entire column.", vbExclamation, _
"Reverse Rows or Columns"
Exit Sub
End If
If Rw > 1 Then
ReDim Arr(Rw)
Else
ReDim Arr(Cl)
End If
Rw = 0
For Each c In rng
Arr(Rw) = c.Formula
Rw = Rw + 1
Next c
Rw = Rw - 1
For Each c In rng
c.Formula = Arr(Rw)
Rw = Rw - 1
Next c
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
Click to expand...


Hello Satish Sir,

Please trace the attached file from my dropbox.

https://www.dropbox.com/s/lkni6btq2rk8pvu/SIMS.xlsm?dl=0

Can you make vba code for getting current stock in issue control register Stock Available colomn after + - of specific code no. Now i am using you suggested code [=VLOOKUP(IssueControlRegister!E12537,Sheet1!$A$4:$B$2000,2,0)-VLOOKUP(IssueControlRegister!E12537,Sheet1!$C$4:$D$2000,2,0)]

Can it is smooth by VBA..

Please do needful
 
You must log in or register to reply here.
Back
Top