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

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

Re: Formatting multiple sheets within workbook.

Show all headers | View raw

Hi Mark,

Am Thu, 30 Mar 2017 06:16:50 -0700 (PDT) schrieb Living the Dream:

> 1. If there are no Blank rows to delete, the code halts.
> ( probably need an exit if criteria not met ).
> 2. It won't sort now
> ( most likely has to do with overall used range, as will not always be rows 11 to 26, sometimes more, sometimes less).
> 3. the double-lines don't appear as before.
> ( clueless as to why this has stopped working ).

try:

For Each sh In ActiveWorkbook.Sheets
   If InStr(sExclShts, sh.Name) Then
      With sh
         Set rng = .Range("A10:O26")
            rng.Borders(xlEdgeBottom).LineStyle = xlNone
            rng.Sort key1:=.Range("F10"), order1:=xlAscending, Header:=xlYes
        
        For i = 11 To 26
            If .Cells(i + 1, "A") <> .Cells(i, "A") Then
               With .Range(.Cells(i, 1), .Cells(i, 14))
                  With .Borders(xlEdgeBottom)
                     .LineStyle = xlDouble
                     .ColorIndex = 0
                     .TintAndShade = 0
                     .Weight = xlThick
                  End With
               End With
            End If
         Next
         For i = 26 To 11 Step -1
            If Len(.Cells(i, 1)) = 0 Then
              .Rows(i).Delete
            End If
        Next i
      End With
   End If
Next sh


Regards
Claus B.
-- 
Windows10
Office 2016

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