selection criteria

[Guest]

Hi

I have two rows of data (header and data row) and I am trying to select only data matching the following criteria

If the data cell is empty, do not include cell or corresponding header cell in selection

Example

header1 header2 header
data1 data

selection should return {A1, A2, C1, C2

I know I can select blank cells by using "Edit", "Go To" ,"special" and then "blanks", but what I am looking for is the inverse of that selection with additional cells discluded based on any value in the cell directly below it. My explanation might not make much sense but I hope the above example is enough to illustrate my problem

Any help much appreciate
/Greg

 

[Jim Rech]

I assume you're looking for a way to do this manually. I don't see anyway
that is more efficient than doing a Ctrl-click. It's easy enough to select
the row 2 cells with Goto Special and Constants (or Formulas if that's what
they are). But I don't think there is any good way to extend those selected
cells up one row. There is a bad (slow) way by tabbing to each selected
cell and pressing Shift-Up).

 

[Guest]

Thank you for your reply

It's quite tedious to do the selections manually as the row extends through heaps and heaps of cells. I was hoping for a way to do the selection through a function in excel but I doubt there is one as it's a conditional selection based on values and relationships between cells. Perhaps there's a third party product you could do this with

Once agai

heading1 heading2 heading
data1 data2

The selection should be heading1 and data1, heading2 and data2

Cheer
/Greg

 

[Jim Rech]

I don't know if you're familiar with macros, Greg, but they are extremely
powerful and can greatly extend and enhance Excel's built-in capabilities.
Here's a macro that I think does what you want. Open the VBE (Alt-F11),
insert a module, and paste this code into it:

Sub GregsSelect()
Dim Cell As Range
Dim NewRg As Range
For Each Cell In Selection.SpecialCells(xlCellTypeConstants)
If Cell.Offset(1).Value <> "" Then
If NewRg Is Nothing Then
Set NewRg = Cell.Resize(2)
Else
Set NewRg = Union(NewRg, Cell.Resize(2))
End If
End If
Next
If Not NewRg Is Nothing Then NewRg.Select
End Sub

Then you can close the VBE. Select all of the first row of your two row
data range. Press Alt-F8 to open the macros dialog and run this macro.
This assumes that the first row consists of constants (not formulas), by the
way.