MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Deleting every other column (variable range)


Posted by Frank on February 05, 2002 7:45 AM

I collect data from an instrument (Mass Spec) that saves the data in format that requires me to delete every other column. For any given experiment the number of columns (Mass spec signals) will vary as well the number of rows (time scale).

Any suggestions for how to write a macro to facilitate the task of deleting every other row?

Thanks, Frank


Posted by faster on February 05, 2002 7:53 AM

A quick way to do this would be to select All Cells,
and sort by columns and then sort by rows. To change
the way Excel sorts select options in the sort wizard.

Experiment with a copy of your data.


Posted by Mark W. on February 05, 2002 7:58 AM

Your posting is confusing...

Do you won't to delete columns or rows?
Must it be done with a macro?

Posted by Mark W. on February 05, 2002 8:00 AM

And, so is mine... let me try again : )

Do you want to delete columns or rows?
Must it be done with a macro?

Posted by faster on February 05, 2002 8:08 AM

VBA Delete Blanks

I would like to calculate the estimated date that the eggs hatched for each individual nest based on the first four columns: Nest #, Date, # eggs, # nestlings. I need a formula that recognizes all the observations for a particular nest and the date at which nestlings first appear, and then returns only one estimated hatch date in a single cell, for each individual nest. For example, for Nest #1, there are 4 eggs, and 4 nestlings appear on 06-May. Please Help!

Posted by faster on February 05, 2002 8:09 AM

VBA Delete Blanks

If you want a quick way of manually checking, use Find & replace, replace it with ZZ099, and then replace it back again. The first time you do it should show you how many replacements were made.

Posted by faster on February 05, 2002 8:12 AM

I think the board went nuclear. Here is my Delete Blanks post.


test on a copy of your work first

Sub DelBlanks()
If MsgBox("Delete blank rows and columns?", vbYesNo) = vbNo Then
Exit Sub
End If

Dim i

Range("A1").Select

For i = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
If Selection.EntireColumn.Text = "" Then
Selection.EntireColumn.Delete
Else
Selection.Offset(0, 1).Select
End If
Next i

Range("A1").Select

For i = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
If Selection.EntireRow.Text = "" Then
Selection.EntireRow.Delete
Else
Selection.Offset(1, 0).Select
End If
Next i

Posted by Frank on February 05, 2002 9:13 AM

Deleting every other column: more info provided

Mark,

My immediate need is to delete columns.
(I am sure at some point I will need to do the same with rows, so I'd like to learn the technique.)

I was assuming a macro would be needed but I am open to other ideas.

Here is more detailed (better?) description of the problem:

When I convert the raw data file from a mass spec instrument into excel, I have alternating columns of data (signal intensity) and instrument parameter (Mass).
Example:

C1= 10 [10 seconds, time]
--------
C2=15 [the mass being analyzed]
C3=0.001 [the raw data]
--------
C4=16
c5=0.005
--------
c6=17
c7=0.125

etc... anywhere up to 300 different Mass signals may be acquired.

each row is one time point [10 sec, 20 sec, etc], the number of rows of data collected is completely variable based on experimental setup

I need to delete every other column (the "mass" column) so that I can more simply graph results and perform further calculations.

I hope this helps better describe my request.

Thanks,
Frank

Posted by Mark W. on February 05, 2002 9:53 AM

Re: Deleting every other column: more info provided

So, let's say that your 1st "mass" column is B:B
and the last "mass" column is F:F...

1. Insert a new row 1:1
2. Enter 1 into cell A1
3. Enter the formula, =NOT(A1)+0, into cell B1,
and Fill to cell F1.
4. Select row 1:1, Copy/Paste Special...Values
5. Select cell A1, choose the Data | Sort... menu
command.
6. Press the [ Options... ] button, click the
"Sort left to right" radio button, and press [ OK ]
twice.
7. Delete the left-most columns containing a zero
in row 1:1 and you're done!!!

Posted by Frank on February 05, 2002 11:49 AM

Re: Deleting every other column: more info provided

Mark, Thanks!!! Your suggestion works like a charm! That very quickly excised the non-data columns.

(Now the problem I have is that with 65 different columns, Excel won't pick up my series labels. It just calls everything: series1, series2, etc.)