Select multiple adjacent cells of multiple cells without selecting

Select multiple adjacent cells of multiple cells without selecting adjacent 
cells one by one. those cells looks like;
|Column A1           |Column A2|
|random characters | A             | 
|sjsjsjsjskkskskskk  | B              |
|wtwuwuwuuusisis | A             |
|wuuuuusiwuwuwiu| A             |
|euuuuuuuuuuuuuuu| B             |
I am trying to select A1 cells that's adjacent of A2 cells without selecting 
A1
cells one by one.

Thank you for your help.
0
Utf
12/19/2009 12:21:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

7 Replies
768 Views

Similar Articles

[PageSpeed] 29

I am trying to select A1 cells that's adjacent of "B" cells without selecting 
A1 cells one by one

> I am trying to select A1 cells that's adjacent of A2 cells without selecting 
> A1 cells one by one.


0
Utf
12/19/2009 12:59:02 AM
You need to do a little research into Excel cell referencing with regard to
rows and columns.

A1 and A2 are cells in Column A, not separate columns.


Gord Dibben  MS Excel MVP

On Fri, 18 Dec 2009 16:21:01 -0800, sjsjsjsjsjs
<sjsjsjsjsjs@discussions.microsoft.com> wrote:

>Select multiple adjacent cells of multiple cells without selecting adjacent 
>cells one by one. those cells looks like;
>|Column A1           |Column A2|
>|random characters | A             | 
>|sjsjsjsjskkskskskk  | B              |
>|wtwuwuwuuusisis | A             |
>|wuuuuusiwuwuwiu| A             |
>|euuuuuuuuuuuuuuu| B             |
>I am trying to select A1 cells that's adjacent of A2 cells without selecting 
>A1
>cells one by one.
>
>Thank you for your help.

0
Gord
12/19/2009 10:40:46 PM
those cells looks like;
|Column A            |Column B|
|random characters | Y           | 
|sjsjsjsjskkskskskk  | Z           |
|wtwuwuwuuusisis | Y           |
|wuuuuusiwuwuwiu| Y           |
|euuuuuuuuuuuuuuu| Z           |
..
..
..
..
continue...
I am trying to select A cells that's adjacent of Y cells without selecting A 
cells one by one, then
copy selected cells to Column C, and
keep rows of selected cells.
those cells will look like;
|Column C            |
|random characters | 
|                          | 
|wtwuwuwuuusisis | 
|wuuuuusiwuwuwiu| 
|                           | 
..
..
..
..
continue...

Thank you for your help.

> those cells looks like;
>|Column A1           |Column A2|
>|random characters | A             | 
>|sjsjsjsjskkskskskk  | B              |
>|wtwuwuwuuusisis | A             |
>|wuuuuusiwuwuwiu| A             |
>|euuuuuuuuuuuuuuu| B             |
>I am trying to select A1 cells that's adjacent of A2 cells without selecting 
>A1
>cells one by one.



0
Utf
12/20/2009 4:01:01 PM
This macro should do what you want...

Sub MoveColAToColCFilterOnColB()
  Dim X As Long, LastRow As Long, Answer As String
  Answer = InputBox("What Column B character(s) do you want to filter on?")
  If Len(Answer) > 0 Then
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    For X = 1 To LastRow
      If StrComp(Cells(X, "B").Value, Answer, vbTextCompare) = 0 Then
        Cells(X, "B").Offset(, 1).Value = Cells(X, "B").Offset(, -1).Value
      End If
    Next
  End If
End Sub

The code will ask you what text in Column B to search on and then perform 
its copy operation.

-- 
Rick (MVP - Excel)


"sjsjsjsjsjsjs" <sjsjsjsjsjsjs@discussions.microsoft.com> wrote in message 
news:DF4022C6-46D7-43E6-948E-D9977A19D915@microsoft.com...
> those cells looks like;
> |Column A            |Column B|
> |random characters | Y           |
> |sjsjsjsjskkskskskk  | Z           |
> |wtwuwuwuuusisis | Y           |
> |wuuuuusiwuwuwiu| Y           |
> |euuuuuuuuuuuuuuu| Z           |
> .
> .
> .
> .
> continue...
> I am trying to select A cells that's adjacent of Y cells without selecting 
> A
> cells one by one, then
> copy selected cells to Column C, and
> keep rows of selected cells.
> those cells will look like;
> |Column C            |
> |random characters |
> |                          |
> |wtwuwuwuuusisis |
> |wuuuuusiwuwuwiu|
> |                           |
> .
> .
> .
> .
> continue...
>
> Thank you for your help.
>
>> those cells looks like;
>>|Column A1           |Column A2|
>>|random characters | A             |
>>|sjsjsjsjskkskskskk  | B              |
>>|wtwuwuwuuusisis | A             |
>>|wuuuuusiwuwuwiu| A             |
>>|euuuuuuuuuuuuuuu| B             |
>>I am trying to select A1 cells that's adjacent of A2 cells without 
>>selecting
>>A1
>>cells one by one.
>
>
> 

0
Rick
12/20/2009 5:19:08 PM
Another way you could approach this is to use formulas in your Column C 
cells. Assuming you will want to "filter" on different text every now and 
then, put the text string you want to look for in Column B in cell D1 (that 
is, put the letter Y in D1) and put this formula in C1 and copy it down as 
far as you like...

=IF(B1=$D$1,A1,"")

-- 
Rick (MVP - Excel)


"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:%23TcxAiZgKHA.6096@TK2MSFTNGP02.phx.gbl...
> This macro should do what you want...
>
> Sub MoveColAToColCFilterOnColB()
>  Dim X As Long, LastRow As Long, Answer As String
>  Answer = InputBox("What Column B character(s) do you want to filter on?")
>  If Len(Answer) > 0 Then
>    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
>    For X = 1 To LastRow
>      If StrComp(Cells(X, "B").Value, Answer, vbTextCompare) = 0 Then
>        Cells(X, "B").Offset(, 1).Value = Cells(X, "B").Offset(, -1).Value
>      End If
>    Next
>  End If
> End Sub
>
> The code will ask you what text in Column B to search on and then perform 
> its copy operation.
>
> -- 
> Rick (MVP - Excel)
>
>
> "sjsjsjsjsjsjs" <sjsjsjsjsjsjs@discussions.microsoft.com> wrote in message 
> news:DF4022C6-46D7-43E6-948E-D9977A19D915@microsoft.com...
>> those cells looks like;
>> |Column A            |Column B|
>> |random characters | Y           |
>> |sjsjsjsjskkskskskk  | Z           |
>> |wtwuwuwuuusisis | Y           |
>> |wuuuuusiwuwuwiu| Y           |
>> |euuuuuuuuuuuuuuu| Z           |
>> .
>> .
>> .
>> .
>> continue...
>> I am trying to select A cells that's adjacent of Y cells without 
>> selecting A
>> cells one by one, then
>> copy selected cells to Column C, and
>> keep rows of selected cells.
>> those cells will look like;
>> |Column C            |
>> |random characters |
>> |                          |
>> |wtwuwuwuuusisis |
>> |wuuuuusiwuwuwiu|
>> |                           |
>> .
>> .
>> .
>> .
>> continue...
>>
>> Thank you for your help.
>>
>>> those cells looks like;
>>>|Column A1           |Column A2|
>>>|random characters | A             |
>>>|sjsjsjsjskkskskskk  | B              |
>>>|wtwuwuwuuusisis | A             |
>>>|wuuuuusiwuwuwiu| A             |
>>>|euuuuuuuuuuuuuuu| B             |
>>>I am trying to select A1 cells that's adjacent of A2 cells without 
>>>selecting
>>>A1
>>>cells one by one.
>>
>>
>>
> 

0
Rick
12/20/2009 5:40:38 PM
Thank you for your help.
Macro really worked.
 re you working for Microsoft or have ou ever worked for Microsoft?
Did Microsoft ever publis A Super Input Routine?
my search in microsoft websites doesn't show any of information about book.

Thank you for your help.

"Rick Rothstein" wrote:
> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
> news:%23TcxAiZgKHA.6096@TK2MSFTNGP02.phx.gbl...
> > This macro should do what you want...
> >
> > Sub MoveColAToColCFilterOnColB()
> >  Dim X As Long, LastRow As Long, Answer As String
> >  Answer = InputBox("What Column B character(s) do you want to filter on?")
> >  If Len(Answer) > 0 Then
> >    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
> >    For X = 1 To LastRow
> >      If StrComp(Cells(X, "B").Value, Answer, vbTextCompare) = 0 Then
> >        Cells(X, "B").Offset(, 1).Value = Cells(X, "B").Offset(, -1).Value
> >      End If
> >    Next
> >  End If
> > End Sub
> >
> > The code will ask you what text in Column B to search on and then perform 
> > its copy operation.
0
Utf
12/23/2009 8:25:01 PM
This is the problem with having your topic split... I just responded to this 
message over in the other newsgroup.

-- 
Rick (MVP - Excel)


"sjsjsjsjsjsjschanged" <sjsjsjsjsjsjschanged@discussions.microsoft.com> 
wrote in message news:A6C90F13-BA30-46F6-8C6C-A97C6FC77976@microsoft.com...
> Thank you for your help.
> Macro really worked.
> re you working for Microsoft or have ou ever worked for Microsoft?
> Did Microsoft ever publis A Super Input Routine?
> my search in microsoft websites doesn't show any of information about 
> book.
>
> Thank you for your help.
>
> "Rick Rothstein" wrote:
>> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message
>> news:%23TcxAiZgKHA.6096@TK2MSFTNGP02.phx.gbl...
>> > This macro should do what you want...
>> >
>> > Sub MoveColAToColCFilterOnColB()
>> >  Dim X As Long, LastRow As Long, Answer As String
>> >  Answer = InputBox("What Column B character(s) do you want to filter 
>> > on?")
>> >  If Len(Answer) > 0 Then
>> >    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
>> >    For X = 1 To LastRow
>> >      If StrComp(Cells(X, "B").Value, Answer, vbTextCompare) = 0 Then
>> >        Cells(X, "B").Offset(, 1).Value = Cells(X, 
>> > "B").Offset(, -1).Value
>> >      End If
>> >    Next
>> >  End If
>> > End Sub
>> >
>> > The code will ask you what text in Column B to search on and then 
>> > perform
>> > its copy operation. 

0
Rick
12/23/2009 8:54:48 PM
Reply:

Similar Artilces:

How can I insert a cell reference in a footer (eg for variable foo #2
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet .PageSetup.LeftFooter = .Range("A1").Text End With End Sub This code should go in the ThisWorkbook code module. -- HTH RP (remove nothere from the email address if mailing direct) "wngg001" <wngg001@discussions.microsoft.com> wrote in message news:8A0F9D9E-269F-45CF-A6E3...

VBA
I have created code that inserts lines into a financial statement but need to have certain cells in other code stay constant. B1 in certain code must stay at B1 regardless of changes to the spreadsheet. Any suggestions?? --- Message posted from http://www.ExcelForum.com/ Use the INDIRECT function. E.g., =INDIRECT("B1") The B1 will remain so regardless of inserting and deleting rows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "bforster1 >" <<bforster1.1cmxzp@excelforum-nospam.com> wrote in m...

Removing attachments from multiple emails
Hi: I searched to get all emails with attachments that are from last year or older. Now I want to select them all and remove the attachments from all of them, but keep the messages. I can do this one email at a time by opening it up, right clicking and choosing "remove", but how do I do this for several emails at once? Is there something in Outlook that will allow me to do this or is there a third party tool? Thanks, Kayda There are many third-party tools to add this functionality to Outlook. = See http://www.slipstick.com/addins/housekeeping.htm=20 --=20 Sue Mosher, Outlook MVP...

Forward multiple emails
I am trying to pull together several emails from multiple sources and forward them out with a new email cover letter. I am trying to avoid forwarding each email separately. I want the emails to arrive together with the new email giving a summary of each. Select all the messages and press Forward. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Hendrix" <anonymous@discussions.microsoft.com> wrote in message news:3bbf01c48f9d$4aabd870$a301280a...

Creating a new document from a multiple paged Excel document
Can I save only one page of an existing Excel multiple page document? If so, how? Thanks to anyone that can take the time to answer/instruct. Right-click the sheet tab that you want, and hit Move or Copy. Choose "Create a copy" and then, from the dropdown, choose "new book". **** Hope it helps! **** ~Dreamboat Excel VBA Certification Coming Soon! www.VBAExpress.com/training/ ******************************** "Lisa" <Lisa@discussions.microsoft.com> wrote in message news:CBBDECC6-2432-41D4-99F2-C1EF10B1EC1F@microsoft.com... > Can I save only one page of...

Excel 2007 exits without asking if I want to save
Hi All, I just started using Excel 2007 recently. I notice it exits when I keys in Alt-F4, without confirming if I want to save, even if I had made changes to the workbook. Is there an option which I can set and make Excel 2007 verify if the user, wants to save before exiting? This is the default in earlier Excel versions. Thanks for help. have you recently run a macro in which you forgot to turn on Application.DisplayAlerts =3D True? susan On Nov 24, 5:25=A0am, ch <c...@discussions.microsoft.com> wrote: > Hi All, > > I just started using Excel 200...

reminders without outlook being open
How can I get reminders and 'new email' indications without Outlook being open? Ideally, when my computer starts up, I'd like whatever service is necessary to accomplish this to start automatically. Thanks! Jesse Aufiero wrote: > How can I get reminders and 'new email' indications without Outlook being > open? Ideally, when my computer starts up, I'd like whatever service is > necessary to accomplish this to start automatically. > > Thanks! > > You can't. The only way (and this only works for email) is to use a third-party email check...

Using a Text / Data output as a cell reference
I am trying to use the end of a column as a divisor and need to convert what i guess is text into an actual cell reference if possible. In column A, say there are 13 data points ending at cell A13. I then try to turn that into a cell reference with the formula ="A"&TEXT(COUNTA(A1:A13,),"0") I put this formula in B1 the output of this is then A13 what i need this to do, is be able to divide any cell by A13. The reason I am going through all this trouble is that there are many columns and each has a different # of data points. I hope this is somewhat clear. Thanks ...

Multiple e-mail account in MS Outlook 2003
I have 4 e-mail accounts set up (all from the same server) and can send from all accounts but I only receive mail for the default account. Any suggestions? Does each account use the same account properties? (e.g. same mailbox name, userid, and password.) "Crazy Squaw" <Crazy Squaw@discussions.microsoft.com> wrote in message news:78F4A600-8A92-4011-8E4C-6094A77DEF4A@microsoft.com... >I have 4 e-mail accounts set up (all from the same server) and can send >from > all accounts but I only receive mail for the default account. Any > suggestions? ...

Change Default When Selecting New Message From Form
Whenever I select New -> Choose Form, it defaults to "Organizational Forms Library" (which is empty). How can I make it default to "User Templates in File System"? Thanks. I've been looking for a way to do that for years, but have never found = one. FYI, there is a newsgroup specifically for Outlook forms issues = "down the hall" at microsoft.public.outlook.program_forms or, via web = interface, at = http://www.microsoft.com/office/community/en-us/default.mspx?dg=3Dmicroso= ft.public.outlook.program_forms --=20 Sue Mosher, Outlook MVP Author of Con...

Cell QA for Bob Phillips
Good afternoon Bob: In reference to your QA: (Which sheet was the active sheet when you right-clicked on the tab? It should have been Data Sheet). Yes, I did as you said, but I am not sure I placed the text string in the right place. I pasted it in View Code on the blank area to the right of "Project / Properties" section but for some reason it is not moving the text from "Data Sheet" to the "Activity Sheet". Is it possible to communicate with you directly so I can get this worked out? Thanks John ---------------------------------------------------...

print multiple attachments outlook 2007
How do I print multiple attachments to an email in outlook 2007 without selecting each one individually? File-> Print...-> select option: Print attached files This will of course print the message itself as well. In order to print only the attachments or a selection of your attachments in one go, you'll need a printing add-in. Some are listed here; http://www.howto-outlook.com/tag/printing -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlo...

printing multiple worksheets in a workbook
I have 13 worksheets in my workbook (Excel 2007). They are all formatted to have page numbers thus "Page x of y". However, when I select all worksheets and try to print them, the printer window reflects 6 documents waiting in the queue. On printing the document even with the collate box checked, the output is not sequential according to the page numbers from either back to front or front to back. This is even more frustrating when printing to pdf (Cute PDF Writer) as the print to pdf creates 6 different files instead of printing all to one pdf file. I need help!!!! ...

how do I get the if function to return a blank cell, not 0?
I am trying to create a chart from a series that contains data for each month. The series is calculated on other worksheets and copied to the worksheet containing the chart. I would like to have the cells for the months that have not been updated yet (now is January, there are 0' in all cells for Feb-Dec) to be blank (to create gaps in the chart) not 0's. Can this be done? Unfortunately, what you want, and what many of us have requested but doesn't exist, is a worksheet function like BLANK() or NULL(). The best we can do is use NA() in a chart's data source, which is...

Serializing multiple objects in one XML document
Each time I serialize an object using XmlSerializer I get a structure like this: <?xml version="1.0"?> <MyType> .. .. </MyType> <?xml version="1.0"?> <MyType> .. .. </MyType> Instead I would like <?xml version="1.0"?> <MyRootThatIWillWriteToTheStream> <MyType> .. .. </MyType> <MyType> .. .. </MyType> </MyRootThatIWillWriteToTheStream> So in other words, can I avoid writing the XML 1.0 decoration header each time? -- Thanks An XML document can only have 1 root, so you have to add ...

How to slot cell values into pre-defined ranges
I would like to slot cell values starting from 500 upto 75000 in ranges (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have 15 bins. pls help Not sure what you want here. Do you want to count the number of times values within a range occur? -- Ian -- "KDD" <KDD@discussions.microsoft.com> wrote in message news:766017CE-A55E-42FF-AD8D-9E74E48CD568@microsoft.com... >I would like to slot cell values starting from 500 upto 75000 in ranges > (bins) like 500-999, 1000-1499, 1500to 1999 etc. How do i do this? I have > 15 > bins. > > pls...

Using Range names in selecting chart series.
Hi, I have a range named DataCount. This will count the number of data points i have in a column. I would like to use this information when specifying the series range in chart. ActiveChart.SeriesCollection(1).Values = "='System 4'!R2C13:R35C13" In this example I need to substitute R35 with DataCount. What is the correct syntax? Thanks in advance, Cherrs! What kind of variable is DataCount? Is it a named range? A VBA Excel.Range object? A string that contains an address? Does it reference R35 or R35C13? - Jon ------- Jon P...

Multiple type class ...
Hi everyone how are you ? So I need a little trick or tip of how to do this: I have some classes in my project like Class A, Class B, Class C, etc; and I have a class that will comunicate me with one of then named Class D for sample see the code below: int ClassD::PegaQuantidadeCanais(LPSTR pClasse) throw(...){ try{ int iloQtde = 0; LPVOID lpvloObj = NULL; // if (strcmp(pClasse, "1 - Dialogic Anal�gica") == 0 || strcmp(pClasse, "2 - Dialogic Digital") == 0){ lpvloObj = new ClassA; iloQtde = ((ClassA)lpvloObj).Peg...

How to change default cell formats
When I open a file, all negative values are shown with a - before them, whilst my colleagues, when opening the same file, the negatives are shown in brackets ie. ( ). How do I ensure that when I open the same file, my negative numbers appear in brackets This is a windows setting. Close excel Change that windows regional setting|currency tab|Negative currency format to show ($1.1) Open excel You may have to reformat: Format|Cells|number tab|Number (adjust the decimal places and comma options) Easty04 wrote: > > When I open a file, all negative values are shown with a - before t...

look up one value that can list multiple corresponding values
I am trying to automate work timesheets by using a spreadsheet that references two different spreadsheets that list job numbers, names and task numbers. I currently have the spreadsheet set up to import and update the two spreadsheets containing job info. I also have the time sheet set up so that when a user types in the job number, the job name automatically appears in the proper location. I need to some how get the spreadsheet to look up the entered job number and return a dropdown list of available task numbers related to that specific job. Task numbers and quantities of task ...

graph creation from table using cells with links not possible
Hello, I have problem with MS Excel 2003. It´s impossible to create a graph from the table which is automatically fullfilled by the links to another sheet. I have to fullfill the table manually (numbers not links to them are physically in the cells) and only then I´m able to create the graph correctly. Is there some possibillity, how to create a graph from the table wich has links instead real numbers in the cells? Thanks for Your ideas. Regards, Bobr. ...

can we attach an image file to a excel cell ,like note pad comment
can we attach an image file to a excel cell ,scanned with a scanner ,like note pad comment ?I have a small database with 50-60 names and i need to save the letters written to each of those names .Please help. You can put the image in a comment: http://www.contextures.com/xlcomments02.html#Picture In article <66D49CAB-F151-42C9-8E55-E5CFC9CF3855@microsoft.com>, "PR" <PR@discussions.microsoft.com> wrote: > can we attach an image file to a excel cell ,scanned with a scanner ,like > note pad comment ?I have a small database with 50-60 names and i need to sav...

Filter Data
Hi all, I have a search form used to look up records based on a number of entry fields. I have, with the help of this forum, created successful searches for all of the text like fields, but I also wanted to have a "# of weeks back" filter as well. My crrent code is: Dim strWhere As String strWhere = "True" If Not IsNull(Me.LastName) Then strWhere = strWhere & " AND ShipTo_LName like """ & Me.LastName & "*""" End If If Not IsNull(Me.Organization) Then strWhere = strWhere & " AND ShipTo_Organization like &quo...

Drop down / validation / lookup into the same cell
Hi I am using 2007 and would like to drop down from a list in a cell and have a lookup in the same cell eg. I have a list of employees and their numbers = emp_list and emp_number emp_list emp_number Joe 1 Jane 2 Bob 3 I want users to be able to select employees from a list then the model will look up the employee number IN THE SAME CELL. Now, I know how to get a drop down list of employees using date validation = emp_list. And I know how to lookup up the emp_number in a seperate cell using INDEX(MATCH()) or VLOOKUP. But I have ma...

Multiple prices on labels?
Hi. I am wondering if there is a way to put multiple prices on the shelf labels. I would like to be able to print a label with the regular price, and price level A on the same label as an example. You can design a label to include pretty much whatever you want. Go into Utilities>Label Designer. It's quite primitive if you're used to WYSIWYG design software, but I have been able to come up with working label formats through trial and error. You should not expect the printed label to look all that much like the design on the screen. But by tweaking the design and printing out ...