Free Microsoft Excel 2013 Quick Reference

Using macro to select a range

Hello,
I am looking for help to setup a simple macro to navigate to a specific
range in excel. Specifically, I need the macro to move to a specific cell,
but move the cell to the top of the screen so everything is visible below the
selected cell.
Currently, I have a simple macro but it shows the cell in the middle of the
screen, cutting off data below.


Post your answer or comment

comments powered by Disqus
I am trying to pull a number from a cell and use it to select a range of cells

Dim days as integer

days = range("A1").value

range(B1:C & days).select

what is the exact syntax including any other line of code required. Assume I know nothing.

Hi...how do I write a macro that selects a range of data and excludes the last 4 rows that contain the word 'Industry' in the first column?

I can select the entire range, but I don't know how to tell the macro to move up 4 rows. The range is not static so I can't have an absolute range in the macro.

Thank you for your time!

...K

I have four ranges named A, B, C, D.
I need to be able to copy (and paste) one of them when its name is
entered in a cell.
What is the macro syntax to get the value in a cell and use it to select
a range, please?
I would attach this macro to a button (which I know how to do) to allow
the user to enter the range name and click the button to copy it to a
fixed location (that I know how to specify)
Thanks!.

I have a form with two date picker controls. I have DTPicker1 as StartDate and DTPicker2 as EndDate. How do I code them to select a range of rows to print?

Thanks you for your help!

I would like to Pause a macro to allow the user to select a range on the spreadsheet and then click OK to continue with Macro.

I tried to use Msbbox, but I can't figure out how to allow user to access the spreadsheet to highlight the desired range.

Any help appreciated. Thanks.

I've seen a lot of posting about using the R1C1 format to select cells when
using VB to program a function into a cell, but not a lot about how to use
the R1C1 format to select a range for use in a VB Macro.

If I am in cell CA91 and I want to select the range starting at CA92 through
CE176, how to I program that into VB? I tried "range(rc1:r4c84).select" and
it doesn't like the colon. I then tried "range(rc[1]:r[4]c[84]).select" and
it bombed on the first open bracket ([).

Any ideas? I can't use the direct addresses since I won't always know where
I'm going to start this macro from.

What I have is a count function that counts the range A42:A100 in cell Z3 on
a sheet called "Start". What I am trying to do is based on that count
function to select a range that I have defined (tag1,tag2,....tag8) on a a
sheet calld "Lables" and print it based on the results of the count function
in cell Z3. I am hoping to be able to do this in a macro.
Any help will be very appreciated

Thank You

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1

Hi

I'm trying to select a range of cells using a counter. The idea is to use a cell to track the number of rows the spreadsheet has, and increment it each time a row is added. I want the macro to evaluate this number and select the range accordingly (i.e. counter+1). I'm struggling to work out how to get VBA to pick up the value of the cell and then use it as a cell reference as part of a range.

I want something like this:

Range("A[counter+1] : AI [counter +1]").Select

Counter += 1

Hope this makes sense, any help would be greatly appreciated
Thanks

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?

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

Hi All,

I am stuck with a problem. Please provide your suggestions.

I am using an inputbox to provide a range to a vba subroutine.
However the inputbox does not allow me to select a range in another
open workbook.


	VB:
	
 Target = Application.InputBox _ 
(prompt:="Select Target cell", Title:="Target", Type:=8) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there any way I can do that?

Thanks,

MG.

Please advise how I write a macro that
would prompt a user to select a range (e.g. a1:e27) to copy and prompt again to select
where to paste.

Thank you.

Hi all,

I have a workbook call Sales.xls

In this workbook, there is a sheet call Sales Staff. In column C, there is a sales person name list. In C2, I want it to be blank. From C3 down-wards, are names of sales person. This name list are varied all the time, i.e. could be 10 people (C2:C12), could be 23 people (C2:25), etc. How to use macro to create a name for this (un-fixed) list ?

Currently, what I have is this, which is done by manually selection...

ActiveWorkbook.Names.Add Name:="SalesStaff", RefersToR1C1:= "='Sales Staff'!R2C3:R7C3"

Please help. Thanks.

Best Regards,
Ricky

For example if I have data like this:

Eggs
NIL
Ham
Bacon
NIL
Cheese
Salmon
Chicken
Turkey
NIL

I want to use macros to copy the cells which will exclude cells which
has NIL... to give me this:

Eggs
Ham
Bacon
Cheese
Salmon
Chicken
Turkey

How can i do it? The only thing I know here is to use
Range("A1:A10").Copy........ which will always include NIL......

How would I setup a macro to select a different cell column within the same
row? I know that I can use the ROW() function to identify the current row.
But I'd like to setup a macro that would goto, let's say, column E of the
current row. I've tried concatenating, using formaulaes like "EROW()", "E +
ROW()", "$E$ROW()" and more but nothing seems to work for me. The idea being
that regardless of current cell, I can make select a specific column within
the same row for cell data replacement purposes.

For example if I have data like this:

Eggs
NIL
Ham
Bacon
NIL
Cheese
Salmon
Chicken
Turkey
NIL

I want to use macros to copy the cells which will exclude cells which
has NIL... to give me this:

Eggs
Ham
Bacon
Cheese
Salmon
Chicken
Turkey

How can i do it? The only thing I know here is to use
Range("A1:A10").Copy........ which will always include NIL......

I'm trying to set up a macro that will allow me to select a range of cells.
The length of the range will vary, based on the results in one column. It
could be a range with no results or possibly 150 results. (I'm sorting based
on a variety of criteria and assigning a value from 1 to 4.)

ie. A B C
address1 name 1 1
address2 name2 2
address3 name3 2
address4 name4 3

How would I tell Excel to grab a range of cells where Column C holds a value
of 2 so that I could grab the data associated with it and dump it elsewhere
for output?

How to select a range of cells one at a time from a workbook? The condition
is that on the first column "A" it shows the refrence number in the first
row then X number of rows down and X number of columns to the right, it
shows the relevant information to that reference number. That X number of
rows and columns are various. I need to select this range to do some
cleaning up within this range and then move on to the next range (reference
number). Also I need to capture the rows number for the "reference number"
cell. The purpose of it is to move all the relevant information to the same
row of the reference number cell and delete all the blank cell after.Thanks

Example:

Ref # color size type status
and so on.......
12456 10
Black Used

25614
Green New

Yes
12
25478
Red
11
Used

No
25478

I am having trouble with a macro that is supposed to perform the following tasks:

Prompt the user to input a cell where the drop down list is to be located
Prompt the user for a range of data to use in the drop down list values
Create drop down list
Hide the rows in which the drop down list values are located

I originally recorded a macro to do this, and then I am tweaking the code accordingly. What is troubling me is getting the macro to select a range from an input box, the using that input to create a list. It's the partcular language included in the .Add command that I do not understand I defined the input box as a range variable celRng, and I am trying to get Formula1: = celRng. But it is not working.

I have attached the code below, and marked in which lines the errors are located:

Sub CreatDropDownList()
'
' CreatDropDownList Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
    Dim celNm, celRng As Range
    On Error Resume Next
       Application.DisplayAlerts = False
           Set celNm = Application.InputBox(Prompt:= _
               "Please select a cell to create a list.", _
                   Title:="SPECIFY Cell", Type:=8)
    On Error GoTo 0
       Application.DisplayAlerts = True

       If celNm Is Nothing Then
           Exit Sub
       
       Else
            With Selection.Validation
                .Delete
                On Error Resume Next
                Application.DisplayAlerts = False
                Set celRng = Application.InputBox(Prompt:= _
                    "Please select the range of cells to be included in list.", _
                        Title:="SPECIFY RANGE", Type:=8)
                On Error GoTo 0
                Application.DisplayAlerts = True

                If celRng Is Nothing Then
                        Exit Sub
                Else
                    .Add Type:=xlValidateList, _
                        AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, _
                        Formula1:=celRng            'THIS IS WHERE THE ERROR HAPPENS
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                 End If
            End With
    End If
    Range("celRng").Select               'ALSO ANOTHER ERROR HAPPENS HERE
    Selection.EntireRow.Hidden = True
End Sub
I feel as if the bug in this code is very simple and that I am not fundamentally understanding something. Any help is greatly appreciated!

DJL

My code is extremely slow because it writes a row by row. I am trying to use arrays to fill a range for a problem which is essentilly this:

Let x = 1 to 10
Let y = 1 to 10
Let R = f (z)

f (z) is calculated for various pairs of x and y (coordinates) such as:
(x1,y1), (x1,y2), (x1,y3)....(x10,y9), (x10,y10).

I am looking for a code that will fill columns A' and 'B' with x and y in pairs (each x with each y) and values of R in column 'C'.

Any help please?

Hello again,

I'm working on the following piece of code:


	VB:
	
Worksheets("Monday1").Select 
For Each x In Monday1 
    Worksheets("Front Page").Select 
    For Each y In Shiftrange 
        If y.Value = x.Value Then 
            y.Offset(0, 1).Select 
            Selection.Copy 
            Worksheets("Monday1").Select 
            x.Offset(0, 2).Select 
            ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone 
        End If 
    Next y 
Next x 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
(The "Monday1" in line 2 is a variable, a specific set of cells on the "Monday1" Worksheet. Shiftrange is another variable, a specific set of cells on the "Front Page" Worksheet.)

This is designed to look at the shift name next to a staff (e.g. 0800-1600), then check for that value on the Front Page, select the data next to the 0800-1600 entry and return it to the first page.

My problem is with the line:

y.Offset(0, 1).Select

Rather than just selecting the one cell to the right of "y" (as it does now), I'd like it to select a range, from the cell one column over, to the cell 56 columns over.

Any help will be most gratefully appreciated!

Thanks in advance,
Andy

PS - reading this back I'm not sure how clear it is, please let me know if I can clarify anything?

Hi!

I need a macro to select a cell (in A collumn) with the same value as other (in B502)

Example: In B502 I have a vlookup formula that gives me "names".

I need a macro that select in the sheet, in the collumn A the cell that contents the same name.

Thank You!!!!!!!!

I am trying to find the last row of data on a spreadsheet and then sort that range.

I use:
Lastrow = Range("A65536").End(xlUp).Row
to get the last rownumber that has data in it.

then
Lastcell = "H"&lastrow
so Lastcell now equals "H75"

so how do I use that to select a range....
Range("A1:lastcell").Select
This doesn't work since lastcell is a variable.

Thanks for your help! :->

Is there a way to use sumif to sum a range of values based on two conditions.
In other words I would like to sum a range of values in col c if values in
col b are equal to "2006" and values f are equal to "proposed".
Thanks

--
hwy


No luck finding an answer? You could always try Google.