?
Solved

Excel VBA 2010 - Determining row and Column sizes

Posted on 2013-12-15
3
Medium Priority
?
378 Views
Last Modified: 2013-12-16
My active cell is inside an island of cells somewhere on my worksheet and I would like to know:
1.  how many rows  and how many columns this region consists of
2.  I'd like a message box saying "Your island consists of 9 rows by 10 columns"
How can I do this in VBA?
0
Comment
Question by:brothertruffle880
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 15

Expert Comment

by:ChloesDad
ID: 39719848
What do you mean by Island of cells. Is it a range that is selected (Highlighted)  and the current cell is somewhere in the selected region?
0
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 1000 total points
ID: 39720383
Insert below code in the Workbook module (ThisWorkbook), and doubleclick on a cell in the region of cells.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    MsgBox "Your island consist of" + Str(ActiveCell.CurrentRegion.Rows.Count) + " rows by" + Str(ActiveCell.CurrentRegion.Columns.Count) + " columns", vbOKOnly, "Island count"
End Sub

Open in new window

0
 
LVL 14

Assisted Solution

by:Faustulus
Faustulus earned 1000 total points
ID: 39721006
Please try this code. It should be installed in the code sheet of the tab on which you wish to have the action. Right-click on the tab and select "View code" to open the correct sheet, then use Copy/Paste to install the code.
Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

    Dim Msg As String
    Dim R As Long
    Dim C As Long
    
    With Target
        If .Cells.Count > 1 Then
            R = .Rows.Count
            C = .Columns.Count
            Msg = "Your current selection has" & vbCr & _
                  String(6, " ") & R & " row" & _
                  IIf(R = 1, "", "s") & " and" & vbCr & _
                  String(6, " ") & C & " column" & _
                  IIf(C = 1, ".", "s.")
            MsgBox Msg, vbInformation, "Selected range"
            Cancel = True
        End If
    End With
End Sub

Open in new window

This code will be activated by right-clicking. It will still allow you access to the normal functions of the right-click if only a single cell is selected. If several cells are selected the message box you asked for will be displayed.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question