| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ending Macro in Last Row within a column of Printable Items
I recorded a macro that sorts data in a certiain way, however sometimes the
data is 100 rows and sometimes it's more or less. How can I record the macro to go to the very last row with printable material within a certain column regardless of how many rows there are? Or is there a quick way to adjust the step in of the macro and re-program it there? Any help is greatly appreciated. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ending Macro in Last Row within a column of Printable Items
Hi Mike
The following code assumes your headers are in row 1. It counts the number of rows with entries in columnA, and the number of columns with data in row 1. It then sorts that selection by ColumnA Ascending followed by column B ascending. Adjust to suit Sub SortData() Dim lr As Long, lc As Long lr = Cells(Rows.Count, 1).End(xlUp).Row lc = Cells(1, Columns.Count).End(xlToLeft).Column Range(Cells(1, 1), Cells(lr, lc)).Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub -- Regards Roger Govier MrMike wrote: I recorded a macro that sorts data in a certiain way, however sometimes the data is 100 rows and sometimes it's more or less. How can I record the macro to go to the very last row with printable material within a certain column regardless of how many rows there are? Or is there a quick way to adjust the step in of the macro and re-program it there? Any help is greatly appreciated. Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ending Macro in Last Row within a column of Printable Items
Thanks Roger, maybe you can help me fix the macro I recorded:
Sub Duplicates() ' ' Duplicates Macro ' ' Keyboard Shortcut: Ctrl+d ' Columns("L:P").Select Selection.Delete Shift:=xlToLeft Range("L1").Select ActiveCell.FormulaR1C1 = "Duplicates" Range("L2").Select Columns("L:L").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],""Duplicate"",0)" Selection.Copy Range("L3:L300").Select ActiveSheet.Paste Columns("L:L").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Cells.Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Clear ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Add Key:=Range( _ "L2:L300"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("export(1)").Sort .SetRange Range("A1:L300") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Right now it goes to row 300 regardless of the amount of rows with data, I want it to only deal with the rows that have data in the, because if I have 200 rows with data, this macro will list 100 rows of blank duplicates. Your help is greatly appreciated. Perhaps, you could even make this macro even better. Thanks again "Roger Govier" wrote: Hi Mike The following code assumes your headers are in row 1. It counts the number of rows with entries in columnA, and the number of columns with data in row 1. It then sorts that selection by ColumnA Ascending followed by column B ascending. Adjust to suit Sub SortData() Dim lr As Long, lc As Long lr = Cells(Rows.Count, 1).End(xlUp).Row lc = Cells(1, Columns.Count).End(xlToLeft).Column Range(Cells(1, 1), Cells(lr, lc)).Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub -- Regards Roger Govier MrMike wrote: I recorded a macro that sorts data in a certiain way, however sometimes the data is 100 rows and sometimes it's more or less. How can I record the macro to go to the very last row with printable material within a certain column regardless of how many rows there are? Or is there a quick way to adjust the step in of the macro and re-program it there? Any help is greatly appreciated. Thanks . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ending Macro in Last Row within a column of Printable Items
Hi Mike
Try Sub Duplicates() ' ' Duplicates Macro ' ' Keyboard Shortcut: Ctrl+d Dim lr As Long lr = Cells(Rows.Count, "L").End(xlUp).Row Columns("L:P").Delete Shift:=xlToLeft Range("L1") = "Duplicates" Columns("L:L").EntireColumn.AutoFit Range("L2").FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],""Duplicate"",0)" Range("L2").Copy Range("L3:L" & lr) Application.CutCopyMode = False Range("L2:L" & lr) = Range("L2:L" & lr).Value ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Clear ActiveWorkbook.Worksheets("export(1)"). _ Sort.SortFields.Add Key:=Range("L2:L" & lr), _ SortOn:=xlSortOnValues, Order:=xlDescending, _ DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("export(1)").Sort .SetRange Range("A1:L" & lr) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Regards Roger Govier MrMike wrote: Thanks Roger, maybe you can help me fix the macro I recorded: Sub Duplicates() ' ' Duplicates Macro ' ' Keyboard Shortcut: Ctrl+d ' Columns("L:P").Select Selection.Delete Shift:=xlToLeft Range("L1").Select ActiveCell.FormulaR1C1 = "Duplicates" Range("L2").Select Columns("L:L").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],""Duplicate"",0)" Selection.Copy Range("L3:L300").Select ActiveSheet.Paste Columns("L:L").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Cells.Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Clear ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Add Key:=Range( _ "L2:L300"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("export(1)").Sort .SetRange Range("A1:L300") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Right now it goes to row 300 regardless of the amount of rows with data, I want it to only deal with the rows that have data in the, because if I have 200 rows with data, this macro will list 100 rows of blank duplicates. Your help is greatly appreciated. Perhaps, you could even make this macro even better. Thanks again "Roger Govier" wrote: Hi Mike The following code assumes your headers are in row 1. It counts the number of rows with entries in columnA, and the number of columns with data in row 1. It then sorts that selection by ColumnA Ascending followed by column B ascending. Adjust to suit Sub SortData() Dim lr As Long, lc As Long lr = Cells(Rows.Count, 1).End(xlUp).Row lc = Cells(1, Columns.Count).End(xlToLeft).Column Range(Cells(1, 1), Cells(lr, lc)).Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub -- Regards Roger Govier MrMike wrote: I recorded a macro that sorts data in a certiain way, however sometimes the data is 100 rows and sometimes it's more or less. How can I record the macro to go to the very last row with printable material within a certain column regardless of how many rows there are? Or is there a quick way to adjust the step in of the macro and re-program it there? Any help is greatly appreciated. Thanks . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ending Macro in Last Row within a column of Printable Items
Roger, thanks for helping me, but that didn't work either. I now get a #REF!
in Cell L1 and a zero in Cell L2 and Cell L3 with no "Duplicates" listed and no sorting afterwards. Anything else you could think of would be appreciated. Thanks "Roger Govier" wrote: Hi Mike Try Sub Duplicates() ' ' Duplicates Macro ' ' Keyboard Shortcut: Ctrl+d Dim lr As Long lr = Cells(Rows.Count, "L").End(xlUp).Row Columns("L:P").Delete Shift:=xlToLeft Range("L1") = "Duplicates" Columns("L:L").EntireColumn.AutoFit Range("L2").FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],""Duplicate"",0)" Range("L2").Copy Range("L3:L" & lr) Application.CutCopyMode = False Range("L2:L" & lr) = Range("L2:L" & lr).Value ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Clear ActiveWorkbook.Worksheets("export(1)"). _ Sort.SortFields.Add Key:=Range("L2:L" & lr), _ SortOn:=xlSortOnValues, Order:=xlDescending, _ DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("export(1)").Sort .SetRange Range("A1:L" & lr) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Regards Roger Govier MrMike wrote: Thanks Roger, maybe you can help me fix the macro I recorded: Sub Duplicates() ' ' Duplicates Macro ' ' Keyboard Shortcut: Ctrl+d ' Columns("L:P").Select Selection.Delete Shift:=xlToLeft Range("L1").Select ActiveCell.FormulaR1C1 = "Duplicates" Range("L2").Select Columns("L:L").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],""Duplicate"",0)" Selection.Copy Range("L3:L300").Select ActiveSheet.Paste Columns("L:L").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Cells.Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Clear ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Add Key:=Range( _ "L2:L300"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("export(1)").Sort .SetRange Range("A1:L300") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Right now it goes to row 300 regardless of the amount of rows with data, I want it to only deal with the rows that have data in the, because if I have 200 rows with data, this macro will list 100 rows of blank duplicates. Your help is greatly appreciated. Perhaps, you could even make this macro even better. Thanks again "Roger Govier" wrote: Hi Mike The following code assumes your headers are in row 1. It counts the number of rows with entries in columnA, and the number of columns with data in row 1. It then sorts that selection by ColumnA Ascending followed by column B ascending. Adjust to suit Sub SortData() Dim lr As Long, lc As Long lr = Cells(Rows.Count, 1).End(xlUp).Row lc = Cells(1, Columns.Count).End(xlToLeft).Column Range(Cells(1, 1), Cells(lr, lc)).Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub -- Regards Roger Govier MrMike wrote: I recorded a macro that sorts data in a certiain way, however sometimes the data is 100 rows and sometimes it's more or less. How can I record the macro to go to the very last row with printable material within a certain column regardless of how many rows there are? Or is there a quick way to adjust the step in of the macro and re-program it there? Any help is greatly appreciated. Thanks . . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ending Macro in Last Row within a column of Printable Items
Hi Mike
Send me a copy of the file roger at technology4u dot co dot uk Change the at and dots to make a valid email address -- Regards Roger Govier MrMike wrote: Roger, thanks for helping me, but that didn't work either. I now get a #REF! in Cell L1 and a zero in Cell L2 and Cell L3 with no "Duplicates" listed and no sorting afterwards. Anything else you could think of would be appreciated. Thanks "Roger Govier" wrote: Hi Mike Try Sub Duplicates() ' ' Duplicates Macro ' ' Keyboard Shortcut: Ctrl+d Dim lr As Long lr = Cells(Rows.Count, "L").End(xlUp).Row Columns("L:P").Delete Shift:=xlToLeft Range("L1") = "Duplicates" Columns("L:L").EntireColumn.AutoFit Range("L2").FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],""Duplicate"",0)" Range("L2").Copy Range("L3:L" & lr) Application.CutCopyMode = False Range("L2:L" & lr) = Range("L2:L" & lr).Value ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Clear ActiveWorkbook.Worksheets("export(1)"). _ Sort.SortFields.Add Key:=Range("L2:L" & lr), _ SortOn:=xlSortOnValues, Order:=xlDescending, _ DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("export(1)").Sort .SetRange Range("A1:L" & lr) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Regards Roger Govier MrMike wrote: Thanks Roger, maybe you can help me fix the macro I recorded: Sub Duplicates() ' ' Duplicates Macro ' ' Keyboard Shortcut: Ctrl+d ' Columns("L:P").Select Selection.Delete Shift:=xlToLeft Range("L1").Select ActiveCell.FormulaR1C1 = "Duplicates" Range("L2").Select Columns("L:L").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],""Duplicate"",0)" Selection.Copy Range("L3:L300").Select ActiveSheet.Paste Columns("L:L").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Cells.Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Clear ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Add Key:=Range( _ "L2:L300"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("export(1)").Sort .SetRange Range("A1:L300") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Right now it goes to row 300 regardless of the amount of rows with data, I want it to only deal with the rows that have data in the, because if I have 200 rows with data, this macro will list 100 rows of blank duplicates. Your help is greatly appreciated. Perhaps, you could even make this macro even better. Thanks again "Roger Govier" wrote: Hi Mike The following code assumes your headers are in row 1. It counts the number of rows with entries in columnA, and the number of columns with data in row 1. It then sorts that selection by ColumnA Ascending followed by column B ascending. Adjust to suit Sub SortData() Dim lr As Long, lc As Long lr = Cells(Rows.Count, 1).End(xlUp).Row lc = Cells(1, Columns.Count).End(xlToLeft).Column Range(Cells(1, 1), Cells(lr, lc)).Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub -- Regards Roger Govier MrMike wrote: I recorded a macro that sorts data in a certiain way, however sometimes the data is 100 rows and sometimes it's more or less. How can I record the macro to go to the very last row with printable material within a certain column regardless of how many rows there are? Or is there a quick way to adjust the step in of the macro and re-program it there? Any help is greatly appreciated. Thanks . . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ending Macro in Last Row within a column of Printable Items
I sent it from my hotmail account. Thanks again Roger
"Roger Govier" wrote: Hi Mike Send me a copy of the file roger at technology4u dot co dot uk Change the at and dots to make a valid email address -- Regards Roger Govier MrMike wrote: Roger, thanks for helping me, but that didn't work either. I now get a #REF! in Cell L1 and a zero in Cell L2 and Cell L3 with no "Duplicates" listed and no sorting afterwards. Anything else you could think of would be appreciated. Thanks "Roger Govier" wrote: Hi Mike Try Sub Duplicates() ' ' Duplicates Macro ' ' Keyboard Shortcut: Ctrl+d Dim lr As Long lr = Cells(Rows.Count, "L").End(xlUp).Row Columns("L:P").Delete Shift:=xlToLeft Range("L1") = "Duplicates" Columns("L:L").EntireColumn.AutoFit Range("L2").FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],""Duplicate"",0)" Range("L2").Copy Range("L3:L" & lr) Application.CutCopyMode = False Range("L2:L" & lr) = Range("L2:L" & lr).Value ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Clear ActiveWorkbook.Worksheets("export(1)"). _ Sort.SortFields.Add Key:=Range("L2:L" & lr), _ SortOn:=xlSortOnValues, Order:=xlDescending, _ DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("export(1)").Sort .SetRange Range("A1:L" & lr) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Regards Roger Govier MrMike wrote: Thanks Roger, maybe you can help me fix the macro I recorded: Sub Duplicates() ' ' Duplicates Macro ' ' Keyboard Shortcut: Ctrl+d ' Columns("L:P").Select Selection.Delete Shift:=xlToLeft Range("L1").Select ActiveCell.FormulaR1C1 = "Duplicates" Range("L2").Select Columns("L:L").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],""Duplicate"",0)" Selection.Copy Range("L3:L300").Select ActiveSheet.Paste Columns("L:L").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Cells.Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Clear ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Add Key:=Range( _ "L2:L300"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("export(1)").Sort .SetRange Range("A1:L300") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Right now it goes to row 300 regardless of the amount of rows with data, I want it to only deal with the rows that have data in the, because if I have 200 rows with data, this macro will list 100 rows of blank duplicates. Your help is greatly appreciated. Perhaps, you could even make this macro even better. Thanks again "Roger Govier" wrote: Hi Mike The following code assumes your headers are in row 1. It counts the number of rows with entries in columnA, and the number of columns with data in row 1. It then sorts that selection by ColumnA Ascending followed by column B ascending. Adjust to suit Sub SortData() Dim lr As Long, lc As Long lr = Cells(Rows.Count, 1).End(xlUp).Row lc = Cells(1, Columns.Count).End(xlToLeft).Column Range(Cells(1, 1), Cells(lr, lc)).Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub -- Regards Roger Govier MrMike wrote: I recorded a macro that sorts data in a certiain way, however sometimes the data is 100 rows and sometimes it's more or less. How can I record the macro to go to the very last row with printable material within a certain column regardless of how many rows there are? Or is there a quick way to adjust the step in of the macro and re-program it there? Any help is greatly appreciated. Thanks . . . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Ending Macro in Last Row within a column of Printable Items
Hi Mike
File returned and sorted. Your test fro duplicates depended upon column C being sorted first. I changed the formula to =IF(COUNTIF($C$2:C2,C2)1,"Duplicate","") I also changed the column to carry out the count of rows to column K. The code now appears to run OK. File returned. -- Regards Roger Govier MrMike wrote: I sent it from my hotmail account. Thanks again Roger "Roger Govier" wrote: Hi Mike Send me a copy of the file roger at technology4u dot co dot uk Change the at and dots to make a valid email address -- Regards Roger Govier MrMike wrote: Roger, thanks for helping me, but that didn't work either. I now get a #REF! in Cell L1 and a zero in Cell L2 and Cell L3 with no "Duplicates" listed and no sorting afterwards. Anything else you could think of would be appreciated. Thanks "Roger Govier" wrote: Hi Mike Try Sub Duplicates() ' ' Duplicates Macro ' ' Keyboard Shortcut: Ctrl+d Dim lr As Long lr = Cells(Rows.Count, "L").End(xlUp).Row Columns("L:P").Delete Shift:=xlToLeft Range("L1") = "Duplicates" Columns("L:L").EntireColumn.AutoFit Range("L2").FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],""Duplicate"",0)" Range("L2").Copy Range("L3:L" & lr) Application.CutCopyMode = False Range("L2:L" & lr) = Range("L2:L" & lr).Value ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Clear ActiveWorkbook.Worksheets("export(1)"). _ Sort.SortFields.Add Key:=Range("L2:L" & lr), _ SortOn:=xlSortOnValues, Order:=xlDescending, _ DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("export(1)").Sort .SetRange Range("A1:L" & lr) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Regards Roger Govier MrMike wrote: Thanks Roger, maybe you can help me fix the macro I recorded: Sub Duplicates() ' ' Duplicates Macro ' ' Keyboard Shortcut: Ctrl+d ' Columns("L:P").Select Selection.Delete Shift:=xlToLeft Range("L1").Select ActiveCell.FormulaR1C1 = "Duplicates" Range("L2").Select Columns("L:L").EntireColumn.AutoFit ActiveCell.FormulaR1C1 = "=IF(RC[-9]=R[-1]C[-9],""Duplicate"",0)" Selection.Copy Range("L3:L300").Select ActiveSheet.Paste Columns("L:L").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Cells.Select Application.CutCopyMode = False ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Clear ActiveWorkbook.Worksheets("export(1)").Sort.SortFi elds.Add Key:=Range( _ "L2:L300"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("export(1)").Sort .SetRange Range("A1:L300") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Right now it goes to row 300 regardless of the amount of rows with data, I want it to only deal with the rows that have data in the, because if I have 200 rows with data, this macro will list 100 rows of blank duplicates. Your help is greatly appreciated. Perhaps, you could even make this macro even better. Thanks again "Roger Govier" wrote: Hi Mike The following code assumes your headers are in row 1. It counts the number of rows with entries in columnA, and the number of columns with data in row 1. It then sorts that selection by ColumnA Ascending followed by column B ascending. Adjust to suit Sub SortData() Dim lr As Long, lc As Long lr = Cells(Rows.Count, 1).End(xlUp).Row lc = Cells(1, Columns.Count).End(xlToLeft).Column Range(Cells(1, 1), Cells(lr, lc)).Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub -- Regards Roger Govier MrMike wrote: I recorded a macro that sorts data in a certiain way, however sometimes the data is 100 rows and sometimes it's more or less. How can I record the macro to go to the very last row with printable material within a certain column regardless of how many rows there are? Or is there a quick way to adjust the step in of the macro and re-program it there? Any help is greatly appreciated. Thanks . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate items based on calendar year ending today | Excel Worksheet Functions | |||
| SUMPRODUCT to count items with duplicates where another column contains two defined items | Excel Worksheet Functions | |||
| How do you add three ending '0's to a column of numbers? | Excel Discussion (Misc queries) | |||
| Ending a macro early conditionally on one cell being blank | Excel Discussion (Misc queries) | |||
| I want to sum a column and exclude those ending with cr | Excel Worksheet Functions | |||
Linear Mode
