• 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.

Formatting using Macro

V

VARGEESH

Member
Hi All,


I have to format a report on daily basis. For that I had recorded a macro and it works quiet good. But when i have less data or more data it wasn't working.


My activities are as follows:-


1. Select the entire sheet.


2. Go to sort > sort by "Column A"


3. "Sort Warning" dialog box displays. On which i need to select "Sort numbers and numbers stored as text separately" And clicked on "OK".


4. Then select "A1".


5. Ctrl + F (Find and Replace) and search for "CALLID".


6. One press "Down Arrow" and press "Shift + Tab" and then "Ctrl + Shift + Down Arrow"

untill the end of the sheet.


7. Then press "Ctrl + -"


8. Again search for "CALLID"


9. And select the entire row by pressing "Shift + Tab"


10. Cut the row and paste it as "Header of the file".


My recorded macro code is

[pre]
Code: 
Sub Formatting()
'
' Formatting Macro
'
' Keyboard Shortcut: Ctrl+q
'
Cells.Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A3411" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:Z3411")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
Cells.Find(What:="CALLID", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Rows("3023:3023").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Rows("3022:3022").Select
Selection.Cut
Selection.End(xlUp).Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
End Sub
[/pre]

Kindly advise me.


Thank you,

Vargeesh
 
I think this is what you're wanting:

[pre]
Code: 
Sub Formatting()
'
' Formatting Macro
'
' Keyboard Shortcut: Ctrl+q
'
Dim LastRow As Long
Dim FoundCell As Range
With ActiveSheet
LastRow = Cells(.Rows.Count, "A").End(xlUp).Row
End With
Application.ScreenUpdating = False
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("A1:A" & LastRow _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:Z" & LastRow)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Set FoundCell = Cells.Find(What:="CALLID", After:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)

'Delete all the rows below
Range(FoundCell.Offset(1, -1), FoundCell.Offset(1, -1).End(xlDown)).EntireRow.Delete

FoundCell.EntireRow.Cut
Rows("1:1").Insert shift:=xlDown
Range("A1").Select
Application.ScreenUpdating = True

End Sub
[/pre]
 
Thank you for your nice effort Luke.


When i ran your code it was thrown an error "400".


Can you please advice me.


Thank you,

Vargeesh
 
Hi Suresh,


I have uploaded the file and below is the path.


http://www.2shared.com/file/lca1YMhS/New_folder.html


The workbook "sampledata.xlsx" contains the raw data.


The workbook "sampledata_FINAL.xlsx" contains the result of the raw data.


Kindly advice me for further proceedings.


Thanks for your help in advance.


Regards,

Vargeesh
 
Hi


I think this line


Code: 
Range(FoundCell.Offset(1, -1), FoundCell.Offset(1, -1).End(xlDown)).EntireRow.Delete


should be


Range(FoundCell.Offset(1), FoundCell.Offset(1).End(xlDown)).EntireRow.Delete


Kris
 
Hi VARGEESH,


Can you please download the below file and check.


http://www.2shared.com/file/L009EZj3/SD_online.html


Thanks,

Suresh Kumar S
 
You must log in or register to reply here.
Back
Top