Free Microsoft Excel 2013 Quick Reference

Select a range of non-adjacent cells in Excel?

I have a series of data values in non-adjacent columns in an excel spreadsheet.
In the following example, assume the | (vertical bar) refers to the start of
a new cell:

F|45|A|30|F|15|F|10

Using the SUMIF formula, I want to add all numbers which are preceded by a
cell containing the letter F.

SUMIF(A1:G1,"F",B1:H1)

What I need to do is specify a range of every second cell in the row
(starting with Cell A1) for validating they equal F, and a range of the
alternating cells (starting in column B1) for the range containing the data
to add. How can I specify these ranges (I can't name each cell individually
as I have more than 30 cells to add up in my real life situation and the IF
function allows selection of no more than 30 values)?


I have a series of data values in non-adjacent columns in an excel spreadsheet.
In the following example, assume the | (vertical bar) refers to the start of
a new cell:

F|45|A|30|F|15|F|10

Using the SUMIF formula, I want to add all numbers which are preceded by a
cell containing the letter F.

SUMIF(A1:G1,"F",B1:H1)

What I need to do is specify a range of every second cell in the row
(starting with Cell A1) for validating they equal F, and a range of the
alternating cells (starting in column B1) for the range containing the data
to add. How can I specify these ranges (I can't name each cell individually
as I have more than 30 cells to add up in my real life situation and the IF
function allows selection of no more than 30 values)?

Hi!

Using VBA I need to set a range of non-contiguous cells from Column A, where column B is not a blank

eg
A B
1 1234 xxxxx
2 2345
3 3456 xxxxx
4 4567 xxxxx

would set range ("A1, A3, A4")

Any ideas on how to do this?
Thanks!
janie

Hi,

I need a macro that would select a range of non-consecutive cells.The problem is that the first cell address is never the same(maybe A21 or A28 or anything). But once the address of the first cell is picked up, all the other non-consecutive cells occur at equal offset distances. May be the user could make the initial selection of the first cell address and a macro could be run to pick-up the rest . How could that be done ?

Hello,

Im trying to create a macro that will do the following. When i record it, it always errors out and doesnt work so im trying to code it manually. Here is an explanation:

I need to Multiply 100 times a range of non-adjacent cells.

ie. the values 0.08125, 0.08000, 0.07875, & 0.07750 are in cells A1,A2,A3,A4 respectively AND 0.07500,0.07625,0.07500,0.07375 are in cells D7,D8,D9,D10.

How can i write a macro that will select all these ranges of cells and multiply the values by 100 so that they end up being 8.125,8.000,7.875,7.750 etc. etc.

Thanks for your help....

What I have in mind to try and do would be easy if I could iterate through
all the cells of a specified block of cells, picking out certain cells that
meet a specific criterion, and adding them to MyRange object. These cells
would probably be scattered over the original block of cells and many would
not be adjacent to any other cell in MyRange. Then I would iterate through
all the cells in MyRange to do things, and drop cells out of MyRange until I
have exactly what I want. Is it possible to set up a range of non-adjacent
cells so they can be iterated through? If so, what's the easiest methods to
use to add or delete a cell from such a range?

Thank you.
Ed

I would like to see the ability to merge non-adjacent cells. In essence, I
am talking about tying two or more cells together so that they behave like
the same cell: you would be able to change data in EITHER ONE and it will
automatically show up in the other cell AND vice-versa.
--
Doyle_D

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

hi,
I have a table like this

jane....kaser...highschool......A
jack....Anderson....college.....B
Jill....Lovin.....college.......C

I want to check if both jack and Anderson in a same row two adjacent cells.
If both jack+anderson in a same row adjacent cells
as per in my table it should return column 4 value (A).
can I use VLOOKUP?
is there another way ?
please note I want to search for a block of two adjacent cells in a table and return its forth column value.

thanks

I would like to see the ability to merge non-adjacent cells. In essence, I
am talking about tying two or more cells together so that they behave like
the same cell: you would be able to change data in EITHER ONE and it will
automatically show up in the other cell AND vice-versa.
--
Doyle_D

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I have ranges made up of non-adjacent cells from one worksheet and I
get a #VALUE! error when I try to do any conditional count or sum
calculation with an array formula on either or both of them. Each
range is a selection of 32 non-adjacent cells from a single column.
The cells contain array formulas that return percentages.

Example of range values
I8: 5.9%
I11: 12.1%
I14: 22.3%
I17: 0.0%

I was able to get values returned from simple functions like Max and
Min but the following example gives me the #VALUE! error:

{=SUM(IF((Rng>0),1,0))}
=COUNTIF(Rng,"<.0125")

Any wisdom would be appreciated!! Thanks

hi guys,

is there a way to select a range of cells in a worksheet and clear the data in them?

i DO NOT want the WHOLE ROW to be deleted, only a few selected cells (they are all adjacent to each other)

and i don't think the ActiveSheet.ranges method works as:
1) the coordinates of the cells in the row to be cleared are determined logically by a VBA sub, i know how to get the coordinates, just dunno how to delete the row
2) this needs to be applied in different areas of the worksheet AND across OTHER worksheets as well.

I'm quite sure I have the logic needed to find the coordinates just need to know how to clear the cells

if there is a way to do it that is similar to ActiveSheet.cells ( row, column ) then it would be best

from my understanding the ranges method only takes in letter and number formats in strings... i need something that takes in number and number

thank you!

1)Use the arrow keys to select the first cell of the range
2)Hold down the [Shift] key while you extend the selection using the up,
down, left, right arrow keys.
3)After the range is selected, and while still holding down the [Shift] key,
press the [F8] key (they release all keys). You will see the word 'ADD' in
the lower right of the status bar.

Repeat above steps until done selecting non-contiguous ranges.

Does that help?

••••••••••
Regards,
Ron

"Jessizzle123" wrote:

> does anyone know the keyboard command used to select a range of nonadjacent
> cells?

I have a file........see enclosed.

I want to count the number of non-blank cells in a range from A to Z along a given row.

Then once the loop is done i want it to tell me the row which had the most non-empty cells but its not working.

Here is a very basic question...In VBA, how do you select a range of cells in order to apply the clear contents command? thx,

j

Hello,
When i select a range of cells and hit delete, a that debug message appears.
The range in question is controlled by the following sytax in VB:

If target.Row < 10 Or target.Row > 13 Then Exit Sub
Application.EnableEvents = False
If target.Column = 8 Then
If IsNumeric(target) = False Then
If Len(target) > 0 Then
target.Offset(0, -2) = ""
End If
End If
End If
Application.EnableEvents = True
End Sub

i have to sometimes select this range of cells all at once and delete, but
how can I prevent the debug message from appearing.

Can I select a range of cells programatically without knowing exactly which
ones they are?

I have a sheet that has a variable number of rows and columns, my code
searches all cells for a certain condition, and when the condition is met, it
needs to select that cell, and the next 8 cells in the same row in order to
copy and paste them somewhere else.

I've tried to use the line: (and various derivatives)

Selection.Offset(0, 8).Select

But this doesn't work.

Can anyone tell me what I'm doing wrong?

Thanks

Neil

Thank you all for the suggestions, it no longer ceases to amaze me how may
different ways there are to do the same thing. Your help is very much
appreciated.

Regards
Mick

"Mick" > wrote in message
...
> This is just a small part of my macro, I do hope the eagle eyed amongst
> you can spot my mistake, all I want to do is select a range of cells in a
> row and copy them?
>
> I don't want to specify the range of cells by naming the rows/columns as I
> need to use the code elsewhere in my macro i.e ("B5:J5")
>
> Sub CopyRowAndAddAsNewRow()
> Dim StartCell As String
> Dim EndCell As String
> Range("A1").Select
> ActiveCell.Offset(4, 1).Select
> StartCell = ActiveCell
> EndCell = ActiveCell.Offset(0, 8)
> Range("StartCell:EndCell").Select
> Selection.Copy
> End Sub
>
> --
> Kind Regards
>
> Mick
>

I have recorded a macro to select a range of cells, merge
them, and wrap the text. The problem I have is the macro
returns to the row on which the macro was recorded. How do
I have the macro select say columns B through F relative
to the row I am on, then perform the rest of the macro.

Thanks in advance!

Kevin

I'll explain what I'm conceptually trying to accomplish, then how I
have tried and failed.

I would like to be able to use ctrl+clicking to select non-adjacent
cells in a column, then reuse that set in other formulas for doing
things like averages or sums of columns.

For example, I ctrl click in column B to select cells in rows 1,2,3,
and 5. I want to maybe do a sum in column C for those rows, and maybe
average in column D for those rows.

I tried giving them a name, then doing soemthing like

Sum(Offset(NamedSet,0,1))

But this gives #Value error as soon as Offset is evaluated.

I just want to be able to redefine the set of rows that the other
formulas should operate on, and have that cascade to the other
formulas. Most of them are sum formulas, so even a solution with sum
formulas would be nice.

I was thinking of adding a column of checkboxes and somehow use the
sumif formula, but the checkboxes are kinda funny in the way they
"float" around and don't seem to actually reside inside a cell.

I'll explain what I'm conceptually trying to accomplish, then how I
have tried and failed.

I would like to be able to use ctrl+clicking to select non-adjacent
cells in a column, then reuse that set in other formulas for doing
things like averages or sums of columns.

For example, I ctrl click in column B to select cells in rows 1,2,3,
and 5. I want to maybe do a sum in column C for those rows, and maybe
average in column D for those rows.

I tried giving them a name, then doing soemthing like

Sum(Offset(NamedSet,0,1))

But this gives #Value error as soon as Offset is evaluated.

I just want to be able to redefine the set of rows that the other
formulas should operate on, and have that cascade to the other
formulas. Most of them are sum formulas, so even a solution with sum
formulas would be nice.

I was thinking of adding a column of checkboxes and somehow use the
sumif formula, but the checkboxes are kinda funny in the way they
"float" around and don't seem to actually reside inside a cell.

I import an excel table from another application.I want to make a macro to:
1.sort table -it'easy
2.from sorted table to select a range of cells until a value from sorted
table is matched.This is the problem.
3.copy selected range in another sheet-it's easy.
Thanks,

Hi All,

I'd like to know if the following is possible within Excel VBA and if so how to implement it please?

I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result.

For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z).

The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).

Hopefully I've explained this ok, but if not I'm happy to help with any more info you need.

Thanks!

I'm trying to select a range of cells using the R1C1 notation. But I'm making an error in the syntax. I know it's really simple, I just don't know what's wrong. Can anyone help?

Thanks.

Hi,
I was wondering how to select, say, the 5 cells below the ActiveCell.
My initial thought was to use the ActiveCell.Offset method but I'm not sure that would let me select a range of more than one cell.
Thanks in advance!

edit: I'm using 2003

Hi.
I seem to be going round and round in circles with this, but I'm sure it should be easy.

I'm just trying to select a range of cells in Sheet2 of my workbook.
I've tried many different bits of code, including:

Dim namesTotal As Integer

namesTotal = 2500

Sheets("Sheet2").Activate
ActiveSheet.Range(Cells(1, 1), Cells(namesTotal, 8)).Select

(According to the Microsoft website, this is supposed to be the way to do it?)

Thanks in advance for your help.