Macro for RearrangeColumnsWithRows and sort them

[CristianFirpo]

Hello Folks
I have this macro that runs wel. Here is my macro

Sub RearrangeColumnsWithRows()
Dim srcRange As Range
Dim destRange As Range
Dim columnsOrder As Variant
Dim i As Long

' Set the source range where the columns and rows are currently located
Set srcRange = Range("A1:BL20411") ' Replace "A1:BL60500" with the range of your row report, including the rows

' Define the desired order of columns
' For example, if you want the columns to be arranged as "C, A, D, B, E, F"
columnsOrder = Array(1,10,8,31,29,18,13,14,15,16,23,24,35,6,5,36,37,38,47,43,45,32,34,61,63,26,2,62,3,4,7,9,11,12,17,19,20,21,22,25,27,28,30,33,39,40,40,41,42,44,46,48,49,50,51,52,53,55,56,57,58,59,60,64) ' Modify the column order as required

' Set the destination range where the columns and rows will be placed
' Make sure to adjust the destination range size
Set destRange = Range("A20412").Resize(srcRange.Rows.Count, UBound(columnsOrder) + 1)

' Rearrange the columns and rows
For i = LBound(columnsOrder) To UBound(columnsOrder)
srcRange.Columns(columnsOrder(i)).Copy Destination:=destRange.Columns(i + 1)
Next i

' Clear the original range
srcRange.ClearContents

End Sub


I would like to add sort column to that macro as Column B ( country Name), Column C (STUDY_SITE_NUMBER), Column D (SUBJECT_ID), and Column F (COST_DATE) and finally deleted the empty cell.

DRUGTRIAL_NAME	COUNTRY_NAME	STUDY_SITE_NUMBER	SUBJECT_ID	ACTIVITY_NAME	COST_DATE
270603​	United States	7801​	E7801002	Concomitant medications	9/27/2022​
270603​	United States	7801​	E7801005	Concomitant medications	10/4/2022​

So execute both macros rearrangeColumnsWithRows and sort column and delete empty cell at once

Thank you very much for your help