Trying to resize a selection

[dudewheresmyvba]

So I have stumbled my way through creating a range that will change with the size of the data I am using, but I am wanting to cut that down some in order to do some formatting. Specifically I want "MyRange" (currently using A1:L17) to not include the top row (1) or the bottom row (17), or the leftmost column (A).

I also need to select that last row and apply separate formatting to it as well.

As I am fairly new to this if there is some explanation to your solution that would be greatly appreciated.

 

[mikerickson]

Try

Set myRange = Range("A1:L17")

With myRange
    Set myRange = .Offset(1,1).Resize(.Rows.Count - 2, .Columns.Count - 1)
End With

The Offset moves the top left cell down one row and right one column (omits the first row and the first column)
The .Resize eliminates the last row and the offset hanging over the original range.

 

[Joe4]

Welcome to the Board!

Try this code, which should give you both pieces of what you are looking for.

    Dim MyRange As Range
    Dim MyRange2 As Range
    Dim LastRow As Long
    
    Set MyRange = Range("A10:L27")
    
'   Exclude first and last row along with fist column
    Set MyRange2 = MyRange.Offset(1, 1).Resize(MyRange.Rows.Count - 2, MyRange.Columns.Count - 1)
    MsgBox MyRange2.Address
    
'   Find last row number in range
    LastRow = MyRange(1, 1).Row + MyRange.Rows.Count - 1
    MsgBox LastRow

Let me know of any questions that you may have.

 

[steve the fish]

Hi. Try this:

Set Rng = Range("A1:L17")
Set Rng = Rng.Offset(1, 1).Resize(Rng.Rows.Count - 2, Rng.Columns.Count - 1)

The first part is obviously your initial range. Then we offset by one row and one column so it all moves down a row and across a column (B2:M18). This has removed the range from the leftmost column and from the top row. We now need to resize the range. We have lost two rows (the top and the bottom) so we use rows.count -2 and also we have lost one column (the leftmost) so we use columns.count -1. Hope that helps.

 

[dudewheresmyvba]

Awesome I appreciate the fast responses.
@mikerickson and @steve the fish: the size of my range changes a lot so sometimes it is 7 rows and sometimes it is 20 something rows (columns are same though) so setting the range to "A1:L17" does not work but setting that to "MyRange" which I got from a CurrentRegion.Select works fine.

@Joe4: I do have several questions. Why set the range to "A10:L27"? What do these first 3 lines mean, I have seen them around but have no idea what their purpose is. And last what would be the purpose of the message boxes? Maybe I wasnt clear enough but I want those rows selected so I can apply formatting to them.

Thanks so much guys I really appreciate it.

 

[mikerickson]

The A1:L17 were just to get the starting range.

All three of us used the same combination of Offset and Resize to eliminate the first row, last row and first column from a range.

 

[Joe4]

Why set the range to "A10:L27"?

It was just an example. It can be anything you desire. I just wanted to make sure that the last row logic would work, even when it is not starting on row 1.

What do these first 3 lines mean, I have seen them around but have no idea what their purpose is.

They are variable declarations. It is good practice to declare all your variables in VBA before using them.
As matter as fact, if you type the phrase "Option Explicit" at the very top of your VBA Editor window (before all your VBA code), it will REQUIRE you to declare all your variables. This is helpful in error debugging and helps quickly identify typos in your variable names.

And last what would be the purpose of the message boxes?

It is just returning the addresses/values to the screen for you to see what they are. They are useful in testing to make sure that your code is doing what you want. But you can remove them from your final code.

Maybe I wasnt clear enough but I want those rows selected so I can apply formatting to them

That is easy enough. With Range variables, just use .Select, i.e.

MyRange.Select

For the LastRow variable, which is an Integer, you can just do this:

Rows(LastRow).Select

However, note that it is not necessary to physically select the ranges in order to work with them (as matter as fact, Selecting/Activating ranges will slow your code down).
For example, if you wanted to format MyRange to be dates, you could do it like this:

MyRange.NumberFormat = "mm/dd/yyyy"

Likewise, if you want to format the last row to be percentages, you could do it like this:

Rows(LastRow).NumberFormat = "0.00%"

 

[dudewheresmyvba]

Resizing a selection, but offset is not working. Reference error?

Hey guys I think I have a syntax error or some sort of reference error.

Range("A1").CurrentRegion.Select
Selection.Name = "MyRange"
Selection.Resize(Selection.Rows.Count + 5, Selection.Columns.Count + 0).Select
Selection.EntireRow.Hidden = False
Selection.Resize(Selection.Rows.Count - 5, Selection.Columns.Count + 0).Select




Set Rng = Range("MyRange")
Set Rng = Rng.Offset(1, 1).Resize(Rng.Rows.Count - 2, Rng.Columns.Count - 1)
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ThemeColor = 5
.TintAndShade = 0.399945066682943
.Weight = xlThin
etc etc..

So here I am unhiding my range plus 5 additional rows. Then I am wanting to offset my selection / range / whathaveyou so that the first and last row and first column are not included to be formatted in the next lines.

If you can explain why your change works and not mine that would be so helpful as I am trying to learn as much as I can.

Duncan

 

[RoryA]

Re: Resizing a selection, but offset is not working. Reference error?

First, please use code tags when posting code.

Second, can you be more specific than that you have some sort of problem? What exactly is or isn't happening?

 

[Joe4]

Re: Resizing a selection, but offset is not working. Reference error?

I merged your two threads together (posts 8 and 9 above). Since this is the same issue, please continue on in the same thread (and do not post the same question multiple times).

Thanks