How do I delete rows and multiple columns automatically?

  • Thread starter Thread starter Dazed and Confused
  • Start date Start date
D

Dazed and Confused

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.
 
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
 
As you have mentioned that you need those functions ubiquitously,
suggest you to use macros. Once you create them in a sheet , you ca
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 toolbar>select control box
3.From the control box that appears on your screen, select comman
button and draw it to your sheet
3.Right click on the command button and click properties from the po
up
4.In the properties window, set caption=Delete Rows (It will help yo
to get notified what this command button is meant for)
5. Now double click on the command button to open the code window an
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 anothe
command button
Sheet1.Columns(<column index>).Delete
7.To move a cell to the end of sheet, use following codes with anothe
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).Value

Above code moves first cell in first row to last cell in first row




Have a nice time

Chris
 
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!
 
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!
 
Back
Top