Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I delete rows and multiple columns automatically?
I generate spreadsheets monthly that require the exact same clean-up/editing
functions each time, such as deleting the first three rows, moving a particular column to the end of the sheet and then deleting various and multiple columns. Is there a way to program these editing functions so I do not have to manually perform same ad infinitum and ad nauseum? Any assistance would be greatly appreciated. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I delete rows and multiple columns automatically?
Try recording a macro when you do the steps manually
(Tools Macro Record New Marco) You could then "playback" the macro by running it And if you need help generalizing your macro, try posting in .programming (paste your macro) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Dazed and Confused" wrote: I generate spreadsheets monthly that require the exact same clean-up/editing functions each time, such as deleting the first three rows, moving a particular column to the end of the sheet and then deleting various and multiple columns. Is there a way to program these editing functions so I do not have to manually perform same ad infinitum and ad nauseum? Any assistance would be greatly appreciated. Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I delete rows and multiple columns automatically?
As you have mentioned that you need those functions ubiquitously, I suggest you to use macros. Once you create them in a sheet , you can use them in any other sheet2 too. Here I present you one sample for deleting first three rows, 1.First make sure that your excel workbook is macro enabled 2.Now right click on the toolbarselect control box 3.From the control box that appears on your screen, select command button and draw it to your sheet 3.Right click on the command button and click properties from the pop up 4.In the properties window, set caption=Delete Rows (It will help you to get notified what this command button is meant for) 5. Now double click on the command button to open the code window and then paste following codes Private Sub CommandButton1_Click() Dim row as Integer Row=1 For row=1 to 3 Sheet1.Rows(row).Delete next End Sub 6.Similarly to delete particular columnuser following code with another command button Sheet1.Columns(<column index).Delete 7.To move a cell to the end of sheet, use following codes with another command button Dim row as integer,col as integer Row=1 Col=1 While sheet1.cells(row,col).Value<”” Row=row+1 Wend Sheet1.cells(row,col).Value=sheet1.cells(1,1).Valu e Above code moves first cell in first row to last cell in first row Have a nice time Chris ------ Convert your Excel spreadsheet into an online calculator. http://www.spreadsheetconverter.com -- Chris Bode |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I delete rows and multiple columns automatically?
Thank you, Max. I had to try it a couple of times, as I had to reduce the
sheet from 114 columns to 14, reformat cells + change column width throughout, but I finally got it right. Thanks again! "Max" wrote: Try recording a macro when you do the steps manually (Tools Macro Record New Marco) You could then "playback" the macro by running it And if you need help generalizing your macro, try posting in .programming (paste your macro) -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Dazed and Confused" wrote: I generate spreadsheets monthly that require the exact same clean-up/editing functions each time, such as deleting the first three rows, moving a particular column to the end of the sheet and then deleting various and multiple columns. Is there a way to program these editing functions so I do not have to manually perform same ad infinitum and ad nauseum? Any assistance would be greatly appreciated. Thank you. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I delete rows and multiple columns automatically?
Thank you. That provided me with much-needed insight into not only how I can
set up this particular project, but other projects of a similar nature. Thanks again, Chris! "Chris Bode" wrote: As you have mentioned that you need those functions ubiquitously, I suggest you to use macros. Once you create them in a sheet , you can use them in any other sheet2 too. Here I present you one sample for deleting first three rows, 1.First make sure that your excel workbook is macro enabled 2.Now right click on the toolbarselect control box 3.From the control box that appears on your screen, select command button and draw it to your sheet 3.Right click on the command button and click properties from the pop up 4.In the properties window, set caption=Delete Rows (It will help you to get notified what this command button is meant for) 5. Now double click on the command button to open the code window and then paste following codes Private Sub CommandButton1_Click() Dim row as Integer Row=1 For row=1 to 3 Sheet1.Rows(row).Delete next End Sub 6.Similarly to delete particular columnuser following code with another command button Sheet1.Columns(<column index).Delete 7.To move a cell to the end of sheet, use following codes with another command button Dim row as integer,col as integer Row=1 Col=1 While sheet1.cells(row,col).Value<€€ Row=row+1 Wend Sheet1.cells(row,col).Value=sheet1.cells(1,1).Valu e Above code moves first cell in first row to last cell in first row Have a nice time Chris ------ Convert your Excel spreadsheet into an online calculator. http://www.spreadsheetconverter.com -- Chris Bode |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I delete rows and multiple columns automatically?
Good to hear. Thanks for rating & feedback
-- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Dazed and Confused" wrote in message ... Thank you, Max. I had to try it a couple of times, as I had to reduce the sheet from 114 columns to 14, reformat cells + change column width throughout, but I finally got it right. Thanks again! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically delete rows with any blank columns | Excel Worksheet Functions | |||
Proper procedures to delete multiple columns with 47,000 rows of data | Excel Discussion (Misc queries) | |||
Delete rows automatically | Excel Discussion (Misc queries) | |||
Automatically insert/delete rows to be x apart | Excel Discussion (Misc queries) | |||
Automatically Break Multiple Rows to Two Columns | Excel Discussion (Misc queries) |