How to define specific range in excel?

JL 41 Reputation points
2022-06-21T06:31:15.05+00:00

I want to define a specific range in excel. For instance, the whole area is A1:M10. Area 1 is the 1st row which is A1 to M1. Area 2 is A2 to L10, and area 3 is M2 to M10. I want to define each area in excel with VBA code. This is just an example. The area range could be anywhere depends on the size of data comes in. The main goal behind this question is to define the area 3 which is the last entire column except for the first cell of it. Appreciate your help and answer in advance.

213253-display.jpg

Microsoft Graph
Microsoft Graph
A Microsoft programmability model that exposes REST APIs and client libraries to access data on Microsoft 365 services.
13,059 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,759 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 119.9K Reputation points
    2022-06-21T19:30:40.133+00:00

    Probably you need something like this:

    Dim ws As Worksheet  
    Set ws = ActiveSheet  
      
    Dim used_range As Range  
    Set used_range = ws.UsedRange  
      
    Dim last_column As Range  
    Set last_column = used_range.Columns(used_range.Columns.Count)  
      
    Dim result As Range  
    Set result = ws.Range(ws.Cells(last_column.Row + 1, last_column.Column), _  
                           ws.Cells(last_column.Row + last_column.Rows.Count - 1, last_column.Column))  
    result.Select  
    

    You can use a specific sheet instead of ActiveSheet.

    Maybe it can be simplified.


2 additional answers

Sort by: Most helpful
  1. Tom van Stiphout 1,696 Reputation points MVP
    2022-06-21T16:40:27.05+00:00

    Select a range, then in the Name box above A1 type the name: "Area 1". You have just created a Named Range.
    You can manage those in Formulas > Name Manager.


  2. Nothing Left To Lose 396 Reputation points
    2022-06-22T13:42:35.32+00:00

    Re: "the whole area is A1:M10. Area 1 is the 1st row which is A1 to M1.
    Area 2 is A2 to L10, and area 3 is M2 to M10."

    Dim Rng as Excel.Range, Rcell as Excel.Range
    Set Rng = Range("A1:M1, A2:L10, M2:M10")

    For Each Rcell in Rng.Cells
    'do stuff
    Next

    '---
    Nothing Left to Lose

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.