VBA Format Multiple Ranges

**hobbiton73** · 11-21-2013, 04:27 AM

Hi, I wonder whether someone may be able to help me please.

I'm relatively new to VB, so a lot of my code is very long winded, an example of which is below:

'***First Range
    LastRow = Cells(Rows.count, "B").End(xlUp).Row
        If LastRow >= StartRow Then
            With Range("B7:D" & LastRow)
                With .Font
                    .Name = "Lucida Sans"
                    .Size = 10
                End With
            End With
        End If

    With Range("D7:D" & LastRow)
        .NumberFormat = "#,##0.00"
        .HorizontalAlignment = xlCenter
    End With
    
    LastRow = Range("B" & Rows.count).End(xlUp).Row
        For i = 7 To LastRow
             With Range("C" & i)
                X = Split(.Value, ",")
                If UBound(X) = 1 Then .Value = Trim(X(1)) & " " & Trim(X(0))
             End With
        Next i

    With Range("B7:D" & LastRow)
        .sort Key1:=.Cells(1, 1), Order1:=xlAscending
    End With
    
'***Second Range

    LastRow = Cells(Rows.count, "G").End(xlUp).Row
        If LastRow >= StartRow Then
            With Range("G7:H" & LastRow)
                With .Font
                    .Name = "Lucida Sans"
                    .Size = 10
                End With
            End With
        End If
    
     With Range("H7:H" & LastRow)
        .NumberFormat = "#,##0.00"
        .HorizontalAlignment = xlCenter
    End With

    LastRow = Range("G" & Rows.count).End(xlUp).Row
        For i = 7 To LastRow
             With Range("G" & i)
                X = Split(.Value, ",")
                If UBound(X) = 1 Then .Value = Trim(X(1)) & " " & Trim(X(0))
             End With
        Next i

    With Range("G7:H" & LastRow)
        .sort Key1:=.Cells(1, 1), Order1:=xlAscending
    End With

The code applies formatting to two ranges within a given sheet in my workbook, but as I mentioned at the start, it's messy and 'long winded'.

I just wondered whether someone with more experience, could possibly look at this please and offer some guidance on how I may be able to streamline the code.

Many thanks and kind regards

**ragulduy** · 11-21-2013, 06:51 AM

You can replace:

With Range("B7:D" & LastRow)
                With .Font
                    .Name = "Lucida Sans"
                    .Size = 10
                End With
            End With

with

With Range("B7:D" & LastRow).font
                    .Name = "Lucida Sans"
                    .Size = 10
            End With

and

With Range("B7:D" & LastRow)
        .sort Key1:=.Cells(1, 1), Order1:=xlAscending
    End With

with

Range("B7:D" & LastRow).sort Key1:=.Cells(1, 1), Order1:=xlAscending

**hobbiton73** · 11-21-2013, 07:12 AM

Hi @yudlugar, thank you very much for taking the time to reply to my post and the guidance.

May I ask, is there a way in which I could streamline the coede any further.

Many thanks and kind regards

**ragulduy** · 11-21-2013, 07:32 AM

If you upload a workbook with a description of what the code is supposed to do, and the code in full. I could try and write it from scratch? I'd expect I would aim up with something very similar to what you have though.

That said, you don't need to do the ranges separately. For example, instead of:

Range("A1").interior.colorindex = 3
Range("A2").interior.colorindex = 3

I could do:

Range("A1,A2").interior.colorindex = 3

If you are unsure of the exact way to build the string to select the different ranges, you could always use union:

Union(Range("A1"),Range("A2")).interior.colorindex = 3

**hobbiton73** · 11-22-2013, 03:56 AM

Hi @yudlugar, thank you for your continued help with this, and my apologies for not replying sooner.

Your offer of re-writing my code is very generous, but I wouldn't want to put you to anymore trouble. I would also like to see this thorugh because I am very keen to gain my knowledge and confidence in writing the code.

Your last post was very inetresting because I wasn't aware of the

Range ("A1,A2")

method of writing for multiple ranges.

All the best and kind regards

**ragulduy** · 11-22-2013, 05:12 AM

for info:
Range("A1,C3")
selects cells A1 and C3
Range("A1","C3")
selects cells a1,a2,a3,b1,b2,b3,c1,c2,c3

**hobbiton73** · 11-22-2013, 05:18 AM

Hi @yudlugar, thank you for coming back to me with this. This is extremely helpful.

All the best and kind regards

VBA Format Multiple Ranges

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Format Multiple Ranges

Re: VBA Format Multiple Ranges

Re: VBA Format Multiple Ranges

Re: VBA Format Multiple Ranges

Re: VBA Format Multiple Ranges

Re: VBA Format Multiple Ranges

Re: VBA Format Multiple Ranges

Thread Information

Users Browsing this Thread

Similar Threads

Linking multiple drop down boxes with multiple ranges using data validation...Errors.

Compiling multiple ranges of cells from multiple sheets into one grand table ?

Worksheet_Change procedure & multiple named ranges: specific cells/ranges fail to update

MultiLookup with Multiple Criterions with Multiple values falling in Date Ranges.

Crteating Multiple GIFS from Multiple Ranges -- need someone to test my code to see why it fails

Bookmarks

Bookmarks

Posting Permissions