Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Set VBA Range As Cells Selected By Cursor


The VBA code (in the code window) runs nicely on the range B10:B1000, but I'd prefer that it only run on a range I define by the cells that are currently highlighted/selected on the active sheet. How should the line of

Set SHOPS = Range("B10:B1000")


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Cursor Movement :: Move To Selected Cells Only
to make cursor to move only on the selected cells.

E7, O21, O22, O23, O24, Z23, Z24, Z26, Z27, Z28, C26, C27, C28 ...

View Replies!   View Related
Paste A Selected Range Of Cells
I think I have the comand to select a range of cells, but can not figure how to paste this selection later in the spreadsheet.

This is how the application works.

I have a spreadsheet that I am using as a template. The first 10 rows have to be repeated later in the same spreadsheet after I make a manual page break.

I the spreadsheet I am doing the following commands:
With xlApplication.ActiveSheet
xlPageBreakManual
.Rows(istartrow).Pagebreak = xlPageBreakManual
End With

View Replies!   View Related
How Do I Total Selected Data From A Range Of Cells?
On worksheet 1 I have Column A with a list of names, which we shall call John, Paul, George and Ringo, listed randomly within 100 cells (A1 - A100).
I have Cells B1 - J100 with 3 options in each cell; Blank, C and NYC.

I need a list on worksheet 2 for each individual person showing;

(1) total number of cells marked C
(2) total number of cells marked C & NYC combined.

View Replies!   View Related
Macro Leaves All The Cells In The Range Selected
I have a worksheet with ever expanding data - rows at the bottom of the data
are continually added. I have a simple macro that sorts all of the data
according to preset parameters and selects the next blank cell in column A,
ready for more data:

Sub Macro5()
Range("SortRange").Select
Selection.Sort Key1:=Range("SortRange"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
Do Until ActiveCell.Value = IsEmpty(True)
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

When running the Macro, this leaves all of the cells in the range 'selected'
(ie; coloured-over). What do I need to add to the Macro to just select the
cell in Column A and remove the highlighting from all the other cells?


View Replies!   View Related
Set Range From Selected Cells/Range
I have a macro which I manually have to change the range in order to run the macro, e.g. Set rngData = Range("B4:I12")

rather than having to change the range for each macro, I was wondering if I could run the macro for the highlighted area. I have tried this, but doesn't seem to work.

Set rngData = Range. CurrentRegion.Select

View Replies!   View Related
Fill Userform Textboxes From Select Cells In Selected Range
Arised from my earlier posting in Populate ComboBox With Specific Sheet Column Range. I have the following working code below, but am having trouble finding coding examples to select specific cells from the selected row (that was found by selecting a ComboBox value)and update TextBoxes with those individual values after the UserForm has been initialized (the bold "GREEN" comment in the code below). I have been able to find plenty of references to update TextBox values to Cells, but that doesn't do me much good in this application since the User needs to verify the old data in these cells before updating them using the UserForm TextBoxes.

I was toying around with several different variations of code (none of which worked properly), so I left it out for clarity of my working code. I'll post up this non-working code as needed, because I really wanted a fresh answer...not what I was trying to do. The attached file should be sufficient to see what's going on

Private Sub UserForm_Initialize()
With Sheets("SR Information")
.Range("A2", .Cells(Rows.Count, "A").End(xlUp)).Name = "MyRange"
End With
SRnumber.RowSource = "MyRange"
End Sub

Private Sub SRnumber_Change()
Dim ServiceRequestNumber As String
Dim c As Range
Dim rngG As Range
Sheets("SR Information").Select
With Selection
ServiceRequestNumber = SRnumber.Value
For Each c In Intersect(ActiveSheet.UsedRange, Columns("a"))
If c = ServiceRequestNumber Then..................

View Replies!   View Related
Set Active Cursor In TextBox (blinking Cursor)
I am working with a VBA userform and several textbox's, setting SetFocus and or TabIndex doesn't leave the box ready to accept input and there is no cursor shown to indicate it is ready to accept input.

View Replies!   View Related
Copy A Selected Range Of Cells On Sheet One To A Range On Sheet Three
Hi. Does anyone know a formula to copy a selected range of cells on sheet one to a range on sheet three when a check box in checked. Ex. copy range a4:j4 on sheet one into a4:j4 on sheet three once the check box for on sheet one is checked?

View Replies!   View Related
Cursor Brings Up Multiple Cells
when i position the cursor over a cell, i get muliple cells highlighted. i enter data and it is entered in the cell i want but the others being highlighted is confusing. i checked and i am not in extended mode.

View Replies!   View Related
Cursor Movement :: To Defined Cells Only
In sheet1, The cursom must move only following cells

A2,A5, C10 C15, D15, E50

Besides this the cursor should not move to any cell.

It must remain between the cells that I described above.



View Replies!   View Related
Cursor Movement :: Move Multiple Cells
Is there a way to make it so when i hit enter my cursor moves 7 cells instead of 1?

View Replies!   View Related
Change Mouse Cursor When Passing Over Specific Cells
I would like to change the mouse cursor when the pointer passes over (without clicking) some cells which have double-click-event script attached.

I know how to change the cursor with a custom one. The pb is for me to identifie that the pointer is over the cell so as to launch the cursor change macro (and reverse when going over another cell).
Excel does that, for ex, with commented cells but can VBA do it also?

View Replies!   View Related
If Statement With Selected Range
I'm trying to create an if statement that have 2 criterias. Here's what I've created:

=IF(COUNT(F2:F4)=('2007Open'!H2:H4),COUNT('2007Open'!B2:B4)=8,'2007Open'!H2:H4)

This statement is returning the #VALUE error.

I guess what I'm trying to do with this statement is to count how many values are not equal to 2007Open!H2:H4 and meets another criteria of 8.


View Replies!   View Related
Expand Selected Range
I need help please. Using a macro, I have selected the range C7:C12. Now I need to keep this selection and expand it 18 columns to the right. This would give me a selection of C7:U12.

The original range changes at various times. Some times it might be C7: C452 etc.

I know it's easy, but I can get the code to keep the selection.

View Replies!   View Related
Autofill Using A Selected Range
I am using the following code to Autofill a formula through a variable range:

Selection.AutoFill Destination:=Range(MyFormulaCell, TerminalCell), Type:=xlFillDefault

MyFormulaCell is the cell with the formula, and TermnialCell is a variable set on a count of rows. This is working great, and I can loop through this for the columns I need to copy just fine by incrementing values, but as I apply this to several sections, it's taking me up to a minute to run the macro. In this "I want it now" day and age, I'm sure my coworkers for whom I am making this will not be happy to wait that long.

Is there a way that I can incorporate a selected range into this method?

I'd like to set MyFormulaCell to:
Range(Selection, Selection.End(xlToRight)).Select
and then autofill the formula down the number of rows contained in TerminalCell.

View Replies!   View Related
Delete Selected Range
My data is in one column as follows:
ABC
(BLANK)DEF (BLANK)(BLANK)
What I want to do is to delete the blank cells and count how many not-empty cells.

The following sub works fine if I put the active cell manually in A1. However, the first range select always give me the "1004" error. Why? I don't have any clue.

Range("a1:a5").Select
Do Until ActiveCell.Value = "stop"
Do Until IsEmpty(ActiveCell)

Selection.Offset(1, 0).Select
Loop
ActiveCell.Delete
Loop

View Replies!   View Related
Count & Sum Of The Selected Range
Count & Sum is one of the most usefull function in the Excel,
I always used this, Is it possible in VBA to create such code that if I Select a range and click on Commandbutton1 automatically one msgbox display with the Count & Sum of the Range Item

eg. If Range A2 to A5 has a number like 50,10,20,10

here if the command button is click automatically one msgbox is display
which showing the

-Total Sum of the Seletced Range is 100 and Total Count is 4


View Replies!   View Related
User Selected Range
I have a form that asks at what row the user wants to start with a selected range then how many rows to select. There will always be 21 columns selected with this range. I am still learning but it has to do with something on how I am setting the Start object. It is not being recognized.

Dim Row As Long
Dim Selection As Long
Dim Start As Range

Row = txtStartRow
Selection = txtHowMany
Set Start = Range("A" & txtStartRow).Select

Range(Start & ActiveCell.Offset(Selection, 20)).Select

View Replies!   View Related
Calculate Only Selected Range
I'm currently on Manual Calculation mode. I have formulas in cells A1 to A10 which has not been refreshed. Can I select only range A3 to A6 and recalculate only those cells?

View Replies!   View Related
Naming A Selected Range
I am trying to name a selected range in VBA in order to use it in a vlookup table. Code I have so far is as follows:

ActiveSheet.Name = TextN
Range("c9"). CurrentRegion.Select
Call formatting
ActiveWorkbook.Names.Add Name = "formTextN", RefersToR1C1 = Worksheets(TextN) & "!" & "r9c2:r45c13"

The formatting works, but the selection does not get named.

View Replies!   View Related
Chart Selected Range
I am trying to link data that has been entered into a inputbox with a range of cells.

My goal is to allow the user to answer: how many people sat the exam?

Therefore when a chart is automatically produced only the cells with the required data are selected.

So, my range is A2:C*

* being the user input.

View Replies!   View Related
Sending A Selected Range Through Outlook
I've seen a few posts recently asking about sending either sending a range or a worksheet in an Outlook email as the body of the message through code. I've been looking at this and think I've come up with something that might work. I'd appreciate it if any of you XL kings and queens would take a look and see if the code works OK on your machine. I've sent a few messages to myself (sad I know ) and they seem to work well.

Here's the code. You need to set a reference to the Outlook object Library AND the Microsoft Scripting Runtime in order for this code to work.

Option Explicit

Sub SendRange()

'Sends a specified range in an Outlook message and retains Excel formatting

'Code written by Daniel Klann 2002

'References needed :
'Microsoft Outlook Object Library
'Microsoft Scripting Runtime...........


View Replies!   View Related
Why Won't This Selected Range Copy To Another Sheet?
I have a button on the Players worksheet that has this
Why would it not work?

Range("NewData").Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy
Sheets("Players").Range("A3").PasteSpecial Paste:=xlValues

I am trying to copy Column B starting at B5 down to the last used cell in column B. into the players sheet starting at cell a3.

I try it and it highlights 4 cells and that is it???
Michael

View Replies!   View Related
Unique Values From Selected Range
I use the following code to extract a unique list of values and paste the list to a specified range:


Public Sub extract_unique()
Dim a As Range, v As Range, w(), i As Long, r As Range
Set a = Selection
Redim w(1 To a.Count, 1 To 1)
With CreateObject("scripting.dictionary")
.comparemode = vbTextCompare
For Each v In a
If Not IsEmpty(v) And Not .exists(v.Value) Then
i = i + 1: w(i, 1) = v.PrefixCharacter & v.Value
.Add v.Value, Nothing ..............
The code gets interrupted at this line:

If Not r Is Nothing Then

with the following error: "Code execution has been interrupted."

When I click debug, the line above is highlighted. If I click the sideways triangle to resume code execution it finishes normally. What can I do to stop my code from being interrupted?



View Replies!   View Related
Copy Selected Range To A New Workbook
I have a code that will allow me to copy three worksheets from an open workbook to a new workbook. That works great, but I only need only a selected range from each of these worksheets to be copied (Range A1:AV60). Here is the code I use to select and copy the worksheets, how do I add a range within this code for each worksheet.

Sheets(Array("Req Page 1", "Req Ext 1", "Req Ext 2")).Copy

View Replies!   View Related
Set Print Area From Selected Range
I have some code which selected a certain amount of columns depending on
whether they contain values or not. Once I have this range set and I select
it I want to set this as the print area. I am not quite sure how to do
this. for example I tried:

rngUnion.Select
ActiveSheet.PageSetup.PrintArea = ActiveCell. CurrentRegion.Address

View Replies!   View Related
Selected Cell Within Named Range?
I want to use the Worksheet SelectionChange event to update part of my worksheet/UI depending on what cell(s) the user has selected.

As an example, if I have two named ranges - $A$1:$B$10 and $C$1:$D$10 - I want to detect whether the user is in range 1, range 2, or neither, then update elements of the UI.

View Replies!   View Related
Vba Return Name Of Range Selected Cell
I have 12 named ranges on a sheet, ArrM1 thru ArrM12.
Each named range is same size, 6 Rows by 7 columns.

Q. If user selects a cell on sheet that is in one of these ranges, what is code to return name of range?
e.g. ArrM1 is cells c10:i15, user selects cell d12; I would like vba to return the name of the range "ArrM1".

Reason, I have the code below so if user selects cell in ArrM1 code is executed, but rather than reproduce code 12 times for each range I thought I could first see where user selects and then change variable for vrange,
Set vrange = wksYearlyCalendar.Range("ArrM1") to
Set vrange = wksYearlyCalendar.Range("ArrM" & x)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CalDaySel As String, CalDateSel As String
Dim vrange As Range
Dim cell As Range

View Replies!   View Related
Copy Selected Range To New Workbook
I am trying to create a method to select the values of contiguous range selections on excel worksheets.

Users will be making new worksheets in new workbooks out of the data from the old sheets.

Currently users make a selection is and copy it to a new worksheet.

Is it possible to retrieve from the 'clipboard', the values from the associated cells?

And then to parse them into columns in a new worksheet?

Earlier I tried this bit of ( it didnt work )

Sub AddNew()
' for passing highlighted text into the form so that I can pass it
'to a new workbook.
Dim PassData1 As String
Selection.Value = PassData1
Set NewBook = Workbooks.Add
With NewBook
.Title = "xxx"
. SaveAs Filename:="xxx.xls"
End With
Range("A1").Value = PassData1
End Sub

View Replies!   View Related
Changing Range Selected With Variable
I have some code that uses offset to select a column of numbers

View Replies!   View Related
Selected Range Address To Variable
I have created a work diary in Excel which is built up in half hour blocks. I am able to create my appointments (usually one or two hour blocks) by selecting a group of cells and running a macro which merges the cells, colours them and puts a border round them.

I now want to be able to delete individual appointments by selecting the appropriate appointment and running a macro to copy and paste from the same range of cells in another worksheet (which is effectively my blank master diary page).
I have tried work out what code I need to put the range of the current selection into a variable in VBA but I'm not having any luck.

View Replies!   View Related
Format When Cell Within Certain Range Is Selected
I have a short macro to remove highlighting from certain fields when data is entered. Here is the full

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(ActiveCell, Range("j18:j500")) Is Nothing Then
Sheets("Internal Transfers").Unprotect Password:=""
With Target.Interior
.ColorIndex = 0
.Pattern = xlSolid
End With
Sheets("Internal Transfers").Protect Password:=""
End If

End Sub

The highlighting only goes away if you hit the Enter key after entering data in the cell. If you use the tab key after entering data, or use the mouse to select a new cell, the highlighting change doesn't occur. I have a feeling this might simply be related to the way I'm invoking this event. I've not worked with the worksheet_change event before, but I've read through various explanations and descriptions of this event and it isn't clear to me what I need to do.

View Replies!   View Related
Assign Variable To Selected Range
What I need is a macro that assigns manually selected cells/ranges (which may or may not be continious) to a Variables.

View Replies!   View Related
Set A Range Based On Area Selected Using Mouse?
I'm trying to set a variable for a range based on the cells selected. For example, I want variable "myrange" to be set to range("A1:D14") if I click on cell A1 and drag it to D14. So far everything I've tried ends up getting the good old "1004 - Application defined or object defined error".

View Replies!   View Related
Generate Random Numbers In The Selected Range
I need to generate random numbers in the selected range. I've looked at quite a few posts on the topic of random numbers, but I can't find one that will fill the selected range with numbers.

View Replies!   View Related
VBA To Identify Top Left Cell Of A Selected Range
if a user has selected a range, how do you, in VBA, identify the:

1. Top left cell
2. Bottom left cell
3. Top right cell
4. Bottom right cell

For example if user has selected the range B5:M30, then we would want to identify in the macro:

1. Top left cell = B5
2. Bottom left cell = B30
3. Top right cell = M5
4. Bottom right cell = M30

View Replies!   View Related
Wrong Range Selected While Sending Email
I am wrote below code to send email using VBA:

Sub SendEmail()
'References needed :
'Microsoft Outlook Object Library
'Microsoft Scripting Runtime
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream
Dim rngeSend As Range, strHTMLBody As String
Dim Range1 As Range, Range2 As Range
alertsSheet = ActiveWorkbook.Name
Workbooks(alertsSheet).Activate
Sheets("STATS").Select

Issue with this code is it is selecting wrong source range while generating email.

MsgBox Range1.Address is giving results as "$A$1:$B$1,$BX$1:$CA$1" and
MsgBox Range2.Address is giving results as "$A$32:$B$38,$BX$32:$CA$38" but
MsgBox rngeSend.Address is giving results as "$A$1:$B$38"

And the email generated as output is having only the data from range "$A$1:$B$38". Is there anything wrong with the way I am specifying the source range ??

I need that the code should replace strHTMLBody with the data in Range1 and Range2. I checked the tempsht.htm file and found that this file also have the same wrong data i.e. data from range "$A$1:$B$38"

Needless to specify that I am working with data having few hidden columns and need to consider only visible data.

View Replies!   View Related
Insert Range-named Row At User-selected Row
On the first row of a spreadsheet template is a <hidden> contiguous series of conditionally formatted cells (range name "stdRow" =production!$1:$1), with formulas, that I want a user to be able to easily insert at whatever row they might be in a worksheet.

If, for example, the user is at D24, then clicks the [InsertRow] button that I have positioned at the top of the worksheet (in a fixed pane), I want the attached macro to insert an instance of "stdRow" directly underneath the user position (at row 25, in this case). The use's position should still be at D24 when the macro finishes. If the user clicks the button multiple times, multiple rows should be inserted (again, without changing the user's position).

This is what I have tried so far:

View Replies!   View Related
Selected Range To Power Point Kinda...
I need to be able to send my selected range of cells (to include: the spreadsheet background, colored cells and all values contained) to Power Point for a daily product. I found this great VBA online and would like to use it but the problem is I keep getting funny results. It doesn't matter what cells I select I keep getting a extra margin on the left side and top of the pasted product in power point. I then have to crop the results... I have looked at the macro (from my noodie eyes) and cannot seem to find a way to adjust this added on margin.

View Replies!   View Related
Determine Comboboxes Inside Currently Selected Range
I have a column with comboboxes in each cell, each with LinkedCell set to the cell the combobox sits inside. Once the selection is made, I hide the combobox with .Visible="False". I want to be able to unhide hidden comboboxes by selecting a range of cells in the column, and then finding the corresponding comboboxes within each cell in this range, and unhide them.

View Replies!   View Related
Sort Selected Range Macro Code
I am copying and pasting from two different " timesheet" spreadsheets into a list. One of the timesheets has blank rows. I am attempting to sort the blank rows to the bottom after I paste the data, but every time I do, it either replaces the top row with "true" or deletes the headers,

Sub SortBlankRows()
Dim rngCurrent As Range
Dim c As Range
Dim inUsedRow As Integer
Set rngCurrent = Workbooks("Payroll Summary.xls").Worksheets(1).Range("A1:J1")
inUsedRow = Workbooks("Payroll Summary.xls").Worksheets(1).Range("D65536").End(xlUp).Row
rngCurrent = rngCurrent.Resize(inUsedRow)
rngCurrent.Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("F1") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End Sub

View Replies!   View Related
Convert Selected Range On All Worksheets To Values
I have a macro that changes user selection from formulas to values:

Dim vCol As Variant

vCol = Application.InputBox("Select Column", Type:=2)
If vCol = False Or vCol = "" Then Exit Sub
Set UserRange = Range(vCol & "9:" & vCol & "35")
UserRange.Value = UserRange.Value

End Sub

I have several workbooks that use this macro, and the workbooks can include several sheets.

Is there's an easy way to change the macro so the user selection is changed in all sheets in the workbook. E.g. if the user selection is column H, the formula is changed to values in all sheets in the workbook.

View Replies!   View Related
Hide/unhide Selected Range In All Sheets
I have several workbooks, and the workbooks can include several sheets. Is there's an easy way to create a macro so the user selection to be hidden or unhidden is hidden or unhidden in all sheets in the workbook. E.g. if the user selection is to hide rows 54-189, the macro hides rows 54-189 in all sheets in the workbook.

View Replies!   View Related
First Value From Right Side In Selected Cells
i try do function, which will be search/monitoring in selected cell (colored yellow, orange, blue) and display (in green/results column) the last value in row colored/selected cell (first value from right side results will be only for row).

View Replies!   View Related
Importing Selected Cells Into VBA
Can you help with how to import into vba just the data in the currently selected cells?

What I want to do is to manually select a range of rows (in a single column) and upon running the macro put the data from each cell into the uppermost cell with each seperated with a comma.

I can manage the combining of the data but don't now the selection is read into VBA.

View Replies!   View Related
Color Selected Cells
When you are on a spreadsheet and you use the arrow keys on the keyboard to move from one cell to another and you can see the cell you are on because of the lines, is there a way to have the color of those lines a different color than black? I would like to be able to arrow from one cell to another and have the lines be red or green so that I can see easier which cell I am actually on. Sometimes it can be hard to see which cell you are. Even if I could make those lines bolder to show up clearer which cell I am on.

View Replies!   View Related
Selected Cells Transport
I want to transport the other column that they not changed rows that I have been selected somerows at the column.

For ex.
at Column A
Rows 2,4,7,9,22,34 .all select cells are going to transport column B.But select rows are not going to change.

View Replies!   View Related
Count Selected Cells
How can I count the number of selected cells on an active worksheet? The number of rows and columns that a selected cell can be on change dynamically.

View Replies!   View Related
Add Prefix To Selected Cells
for a small online database I have a column that lists nationalities:

French
Spanish
American
Latin American
(etc etc)

I need to add a prefix to all nationalities, for example:

Artist Nationality///French
Artist Nationality///Spanish
Artist Nationality///American
Artist Nationality///Latin American
(etc etc)

Is there a way I can select the 700 cells and do this in one shot? I can't add formulas because I will have to paste all this in text pad and then upload it.

View Replies!   View Related
Hide All But Selected Cells
I want to be able to select a range on my spreadsheet, click a button, and have everything that is not selected hide itself. I've tried using intersect, but I'm not sure how to (quickly) loop through all of the columns and rows to see if my range is contained within.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved