Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109956

Re: Formatting multiple sheets within workbook.

Show all headers | View raw

Hi Garry

I had a play with your code and it seems to work, but!!!!

It keeps stopping at the same line as I mentioned in the other code you supplied for another project I had with the Error 9 Subscript out of range..

If Not vData(n + 1, 1) = vData(n, 1) Then

This is the code thus far. I have still yet to get to the part where it deletes blank rows.

Sub Sheet_Magic()

Const sExclShts$ = "'TMS DATA', 'SUPPORT DATA', 'TMS AUDIT', 'TENDER AMOUNTS', 'ROUTE SUMMARY', 'RUN SHEET TEMPLATE', OVERWEIGHT, 'COMMIT LOG', 'TENDER LOG'"

    For Each sh In ActiveWorkbook.Sheets
        If Not InStr(sExclShts, sh.Name) Then
        
            Dim n&, vData
            vData = ActiveSheet.UsedRange
        
            With ActiveSheet
                .Range(Cells(11, 1), Cells(26, 14)).Sort key1:=.Cells(4, 6)
            End With
        
                For n = 10 To 26
                    If Not vData(n + 1, 1) = vData(n, 1) Then
                        With Rows(n + 1).Columns("A:N").Borders(xlEdgeBottom)
                            .LineStyle = xlDouble
                            .ColorIndex = 0
                            .TintAndShade = 0
                            .Weight = xlThick
                        End With
                    End If
                Next n
                
        End If
    Next sh

End Sub

Any thoughts as to why it keeps halting on the vData line..??

Look forward to hearing from you soon.

Many thanks again.
Mark.

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-28 03:40 -0700
  Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-28 03:42 -0700
  Re: Formatting multiple sheets within workbook. GS <gs@v.invalid> - 2017-03-28 10:56 -0400
    Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-29 03:40 -0700
      Re: Formatting multiple sheets within workbook. GS <gs@v.invalid> - 2017-03-29 11:48 -0400
        Re: Formatting multiple sheets within workbook. GS <gs@v.invalid> - 2017-03-29 17:46 -0400
          Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-30 03:25 -0700
            Re: Formatting multiple sheets within workbook. Claus Busch <claus_busch@t-online.de> - 2017-03-30 12:48 +0200
              Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-30 06:16 -0700
                Re: Formatting multiple sheets within workbook. Claus Busch <claus_busch@t-online.de> - 2017-03-30 15:48 +0200
              Re: Formatting multiple sheets within workbook. GS <gs@v.invalid> - 2017-03-30 11:10 -0400
                Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-31 04:03 -0700
                Re: Formatting multiple sheets within workbook. GS <gs@v.invalid> - 2017-03-31 07:13 -0400
              Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-31 03:51 -0700
                Re: Formatting multiple sheets within workbook. GS <gs@v.invalid> - 2017-03-31 07:08 -0400
    Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-29 03:52 -0700
  Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-03-29 02:43 -0700
  Re: Formatting multiple sheets within workbook. Living the Dream <noodnutt@gmail.com> - 2017-04-03 04:12 -0700

csiph-web