asked on

Dynamic Ranges in VBA

Hello,

I am currently working on developing a VBA code and I got stuck on the following problem.

I have multiple VBA Codes which helps me format a table and the content within that table. First of all I am defining the table borders. Then, I am doing some formats on the cell selection. Is there a way to run the VBA without selecting the cells? If he finds borders to left and right on that row where I have the active cell, the to run the VBA between that borders?

Here are the codes on how I format the table borders, and then I set some format on some cells.

E.g. if I have D2 as active cell ( in a table from B2:H10) and I run macro to format as Subheading, the format will apply between B2:H2

Sub Callback1(control As IRibbonControl)

Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.font.Name = "Calibri"
Selection.font.Size = "9"
Selection.Interior.ColorIndex = 2.5
Selection.RowHeight = 12
Selection.VerticalAlignment = xlCenter
End Sub

Sub Callback2(control As IRibbonControl)

Selection.RowHeight = 12
Selection.font.Name = "Calibri"
Selection.font.Size = "9"
Selection.Interior.ColorIndex = 2.5
Selection.font.Bold = True
Selection.font.Color = vbBlack
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Worksheets("Tabelle1").Activate
Selection.VerticalAlignment = xlCenter

End Sub

Many thanks for your help!
Roxana

SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roxana Vlad

ASKER

Many thanks for your reply.

My Excel does not have an actual Table in it. I am building one using VBA code by adding borders to a Region in Excel, and then Step by step, using other macros I am formatting the entire Table(or Region ).
This Region may change from one use to another, so I need to establish a Dynamic Range for my code. I will add a picture to make it more understandable. Also, for Heading and Sub Heading, I am kind of building my one with my formats.

If active cell is F6 and I run macro to format into Calibri, I want to actual format from E6:I6 the entire row with Calibri.

There are times when I have to format a Table with 30 columns and I don't want to have to select 30 cells to format.

Thank you!

SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roxana Vlad

ASKER

Hi Roy Cox,

Thanks a lot for your reply!

I did run your code but it does not make any changes to my data. I get the boxes with questions about the numbers of columns, rows and the starting cell, but id doesn't do anything.

Roxana Vlad

ASKER

Hi Shums,

Thanks again for your help!

Here is what I get when running the first code:

Shums Faruk

Hi Roxana,

For any dynamic range, you need to have data first, you cannot expect code to run dynamically on blank cells. Else you need to specify the range.

Shums Faruk

Have you tried running the code in sample I provided?

Roxana Vlad

ASKER

And this is for the second one:

Roxana Vlad

ASKER

Hi Shums,

I did tried your code. But for e.g. I have one row which I need to only format by adding a color for cells with no data in it. Isn't there a way to check if there are borders to left and right and then to run the macro in between these borders? This is an example only for one border.

So to have something like: If border is found left and border found right then run macro. Does it make any sense what I am saying?

Sub DynamicRange()

Dim i As Integer

i = 1

For i = 1 To 23

'checks if a border exists in the edge left
If Range(Cells(i, 1), Cells(i, 1)).Borders(xlEdgeLeft).LineStyle _
<> xlNone Then

Cells(i, 2) = "True"
Else
Cells(i, 2) = "False"
End If
Next i
End Sub

Roy Cox

The code to change the formatting is yours, which you said worked.

The code to get the range is mine defines the range based on the answers provided.

I really don't see why you do not format the Range as a Table. There are so many advantages and you can even define your own design.

Shums Faruk

Hi Roxana,

In attached, if you click Format Header, it will prompt you to select Header Cell, please select the first cell, which is E4.
If you click Format Data, it will prompt you to select Data Range First Cell, please select E5.

Hope this is what you wanted initially.

You don't need code to check if cells has borders or not, you can just remove all formatting before applying new one, which I have included in attached.

Kindly note, you need to have data to run above code.
FormatHeader-Data_v2.xlsm

Roxana Vlad

ASKER

Thanks a lot for all your answers!

My range won't have data in each cell. There can be rows that only need a font color change. My table can have even 50 columns and I didn't wanted to select each row that needs a format, for 50 columns...

I will try and re-do my code and use a Table instead of a Region surrounded by borders.

Roxana

Roy Cox

The code I provided helps you set a dynamic range and formats using your code.