Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA Selecting Multiple Cells

This one should be easy. I need to select multiple cells in Excel. The macro recorder gives me:

Range("A11:E11").Select

However, I'm selecting in a loop and don't know the row number.


Post your answer or comment

comments powered by Disqus
I'm trying to select multiple cells between named cells for a mass text style change, but can't figure it out. Can anyone help please?

This is what I'm trying to do...

StoreName as Range, TransactionDate as Range,

ActiveCell.Value = "Home Depot"
Set StoreName = ActiveCell
ActiveCell.Offset(0, 4).Select
ActiveCell.Value = "Date"
Set TransactionDate = ActiveCell

Range ("StoreName":"TransactionDate")select
Selection.Font.Bold = True

End Sub

Thanks

How to stop selecting multiple cells with one click in Excel 2007

When I select multiple cells in a spreadsheet that are not adjacent to each
other, I can't see what cells are being highlighted unless I go through the
extra step of changing the cell colors. Sometimes, I want to quickly add up
multiple cells by simply highlighting them. I can't do that unless I change
the color or write in a sum function. For example, if I click on cells A1
and C10 and d25 to add them up, I can only see that my cursor is on the last
clicked cell, so I can't tell if I've highlighted all three cells. It's
driving me crazy and slowing me down. Any thoughts?

In excel (and a number of other applications) holding down control and
clicking with the mouse doesn't let me select multiple cells, photos, files
etc.I think I may have fiddled with the keyboard settings somehow but how do
I put it all right? I don,t appear to have a CD with my keyboard so I can't
just re-plug and play.

Currently when I select multiple cells with my cursor the "highlighted" color
is very faint. It's nearly impossible for me to see especially if there is a
glare on my screen. Is there a way to make the shading darker when I select
multiple cells so I can see what I've selected?

I am trying to sum all numbers <0 using the SUMIF formula in Excel 2003.

FORMULA: =sumif(range,criteria,sum_range)

I got it to work, but I have cell ranges to total that are not next to each
other (such as A5:A15 and C5:C15).
When selecting the ranges for the formula, Excel writes them A5:A15,C5:C15

because of the comma (,) between the cell ranges Excel is reading the A5:A15
as the range and C5:C15 as the critera. How can I get this formula to allow
me to select multiple cell ranges?

All help is appreciated.

Thank you.

Hi guys,

Looking for some help on this one, I have the following code which selects a cell at the end of a range and then enters the word dummy and fills the adjacent cell grey. How can I modify this code to select multiple cells to be shaded for example I want to have Cells(5 + Range("no_layers") + 2, 2) & Cells(5 + Range("no_layers") + 2, 1) without entering the same code over and over.

Thanks

Andy

Sub Dummy_Layer()

Sheets("Output").Select

Cells(5 + Range("no_layers") + 2, 4) = "Dummy"

Cells(5 + Range("no_layers") + 2, 3).Select


    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
        .PatternTintAndShade = 0
    End With
    



End Sub


When I am selecting multiple cells (using the ctrl button) the colour scheme used by excel to highlight these cells is almost impossible to see. DOes anyone know how to change this please?

Hi

How do I reference multiple ranges using named ranges?

Instead of selecting:
Code:
I want to utilise named ranges i.e. instead of AP2 & AS2 I want to use 
Code:
and 
Code:

I tried the following with no luck:
Code:
Any ideas?

Code in sheet required to prevent from selecting multiple cells or deleting multiple cell values.

User should able to select any single cell to work and should be able to delete any single cell value.

Is there is anyway to promt user msgbox informing multiple selection not available??

Hello,

I'm not sure if its possible or not, im trying to built an audit trail for excel, however, as far as I have researched it seems you can only do it for one cell at a time, so im wondering if there is a way to get the value when multiple cells are changed and record them one by one.

I have put a detection message when multiple cells are select perhaps thats the only thing to do, if so is there any way to keep the PreviousValue instead of deleting them when multiple cells are selected.

This is the code i have managed to get so far. Im no expert in VBA, just putting things together, and trying to make sense of what i find online and the basic idea.

Also I was hoping perhaps someone can guide me how to add more error checks, making it less prone to fail.

Thank you in advance.


	VB:
	
 
Dim PreviousValue 
Dim NR As Long 
Dim ReasonInput As String 
Dim x As Long 
 
 
 
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    PreviousValue = Target.Value 
End Sub 
 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    If Intersect(Target, Range("A:BA")) Is Nothing Then Exit Sub 
    If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then 
        MsgBox ("Multiple Selection Detected." & vbNewLine & " Please delete the initial value and modify one cell at a
time.") 
        With Sheets("AuditTrail") 
            NR = .Range("B" & Rows.Count).End(xlUp).Row + 1 
            .Range("B" & NR).Value = Now 
            .Range("C" & NR).Value = Environ("username") 
            .Range("D" & NR).Value = ActiveSheet.Name 
            .Range("G" & NR).Value = Environ("Username") + " has select multiple cells. Value could not be determined" 
            .Range("I" & NR).Value = Target.Address(False, False) 
            Application.EnableEvents = True 
        End With 
        Exit Sub 
    End If 
     
    With Sheets("AuditTrail") 
         
        ReasonInput = InputBox(Prompt:="Cell will be updated with " & vbNewLine & Target.Value & vbNewLine & "Please enter a
reason to proceed." & vbNewLine & "A reason must be added to proceed", Title:="Reason") 
        If ReasonInput = vbNullString Then 
            Application.EnableEvents = False 
            If PreviousValue = "" Or Target.Value  PreviousValue Then 
                Target.Value = "" 
                Range(Target.Address).Value = PreviousValue 
                Application.EnableEvents = True 
                Goto Out 
            Else 
                Target.Value = PreviousValue 
                Application.EnableEvents = True 
                Goto Out 
            End If 
        End If 
         
         
        Application.EnableEvents = False 
         
         
         
        NR = .Range("C" & Rows.Count).End(xlUp).Row + 1 
        .Range("B" & NR).Value = Now 
        .Range("C" & NR).Value = Environ("username") 
        .Range("D" & NR).Value = ActiveSheet.Name 
        .Range("E" & NR).Value = Range("E" & Split(Range(Target.Address).Address(1, 0), "$")(1)).Value 
        .Range("F" & NR).Value = Range(Split(Range(Target.Address).Address(1, 0), "$")(0) & "1").Value 
        .Range("G" & NR).Value = ReasonInput 
        .Range("H" & NR).Value = Target.Address(False, False) 
        .Range("I" & NR).Value = PreviousValue 
        .Range("J" & NR).Value = Target.Value 
        On Error Resume Next 
         
         
    End With 
     
Out: 
    Application.EnableEvents = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to make this code more effecient. This If Then statement just checks the cell above to see if it is color 37 and then makes it color 2 and vise versa. Although when it formats the cells one at a time, is there a way to select multiple cells to the simplify the code. Any help is appreciated.
Thanks


	VB:
	
 
    With Selection.Interior 
        .ColorIndex = 2 
        .Pattern = xlSolid 
    End With 
    With Selection.Offset(0, 4).Interior 
        .ColorIndex = 2 
        .Pattern = xlSolid 
    End With 
    With Selection.Offset(0, 3).Interior 
        .ColorIndex = 2 
        .Pattern = xlSolid 
    End With 
    With Selection.Offset(0, 2).Interior 
        .ColorIndex = 2 
        .Pattern = xlSolid 
    End With 
    With Selection.Offset(0, 1).Interior 
        .ColorIndex = 2 
        .Pattern = xlSolid 
    End With 
End If 
 
 'make blue alternating lines
If ActiveCell.Offset(-1, 0).Interior.ColorIndex = 2 Then 
    With Selection.Interior 
        .ColorIndex = 37 
        .Pattern = xlSolid 
    End With 
    With Selection.Offset(0, 4).Interior 
        .ColorIndex = 37 
        .Pattern = xlSolid 
    End With 
    With Selection.Offset(0, 3).Interior 
        .ColorIndex = 37 
        .Pattern = xlSolid 
    End With 
    With Selection.Offset(0, 2).Interior 
        .ColorIndex = 37 
        .Pattern = xlSolid 
    End With 
    With Selection.Offset(0, 1).Interior 
        .ColorIndex = 37 
        .Pattern = xlSolid 
    End With 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Version: XP SP2 / Excel 2003

I want to copy a range of cells and paste them elsewhere on the sheet. But the range will be different every time, highlighted by selecting the cells, then starting the code.

The Activecell command works successfully with one cell. But I can't seem to work it out between the Offset command or ActiveRange(?).

Any help would be greatly appreciated.

...>

When I am using Excel and want to select a single cell with my mouse, I click
on the cell but I get multiple cells instead. Of course I can still use the
keyboard to change its size, but that takes too long.

Unless I hit enter or tab, I often get 2 or more cells when I'm trying to
select only one cell. eg. I click in a cell to enter info or to add or
delete a row and I get 2 or more columns selected or 2 or more rows. The
only way I can select one cell at a time is to right click in the cell. How
do I prevent the multiple selection of cells from happening. This is very
time consuming.

Somehow I am getting multiple cells, rows, etc. selected with a single
click. Any ideas?

TIA

Hi,

does anybody know through VBA how to select mutliple cells in different
columns & rows together. the manual method is to press cntrl key & click the
cells.

the code i have written asks for a text input to search similar text(names)
spread in different columns & rows in a worksheet & gives the count for the
no. of names found.

sub findtheperson()
dim WS as worksheet
set WS=Worksheets("Sheet1")
dim sname as string
dim Cnt as Long
Cnt=0

With WS
sname=LCase(Inputbox("Enter the name to Count: "))
With WS.Range("a1:iv65536")
set c = .Find(sname,Lookin:=xlValues)
If Not c Is Nothing Then
firstaddress=c.address
Do
Cnt=Cnt+1
' Cells.SpecialCells(xlCellTypeConstants).Select
Set c= .FindNext(c)
Loop While Not c Is Nothing And c.address firstaddress
End If
MsgBox "The Name selected: "& sname &", Name Count: " & Cnt
End With
End With
End sub

This just finds the count of the no of occurences of the similar names in
the worksheet, but does not do a multi-select. Can use ColorIndex property
to color code the found names, but donot want to do that, instead do a
multi-select. Cant use SpecialCells property as it selects all names on the
sheet & also cannot use Union Method as it requires the more than 1 ranges
to be defined. like mymultiRange=Union(Range1,Range2,....)

Any ideas?????????

Rgds,

Eijaz

Gentlemen,

Using VBA, is it posible to select all the cells in column A which their
value fall in between 5 and 10
i.e select all cells in columnA >=5 and =< 10

Hi,

I have two questions.

1) Is it possible to prevent the user from selecting multiple cells? I only allow the user to select 1 cell at one time.

2) I have protected some cells which I do not wish the user to edit. Is there a way that I can even prevent the user to select those particular cells?

My worksheet with macros gets messed up when a user selects either multiple cells from column F or selects multiple cells in any one row (but only if column F is included) in the range C17:O116 and hits "delete". I will either do an exit sub, an application.redo, or something similar so that this doesn't happen, but I don't know how to write the code to trigger these events. So I need a way to determine "if the user selects multiple cells in column F and hits delete" or "if a user selects multiple cells in any row from 17 to 116 that includes the cell in row F, and hits "delete."" My defined ranges so far are below in case that helps. Thanks!

Set Rng = Range("F17:F116")
Set rng2 = Range("K17:K116")
Set Rng3 = Range("L17:L116")

Greetings...
I have a WS that contains code under 'Private Sub Worksheet_Change(ByVal
Target As Range)'

I only want to run the code if the user is changing contents of a single cell.

How do you detect if the user has selected multiple cells or not?

Not sure if it is just a error in my computer or if there is a reason for this. Periodically, when I am working on a spreadsheet & I click a cell it will select multiple cells and keep doing so. Sometimes it will eventually quit or I have to save, close and then re-open.

Any ideas?

Thanks in Advance

This is probably a simple solution for Excel savvy users, but I am at a loss.

I use Excel mainly for creating & updating simple logs, and the problem I keep running into is that when I click in a cell, it will automically select & highlight multiple cells below the one I am trying to work in. It will select anywhere from 4-5 cells up to 13. I don't know what this function is, and I don't know how to fix it. But it makes it very difficult to navigate and cut/paste or change a formula.
Any help would be appreciated!


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