How to make the range only as long as needs to be ...

:banghead:

Code:

---

Sub Zflex_For_Customer()
Application.ScreenUpdating = False
Range("E6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("E6:Q90").Select
    Selection.Sort Key1:=Range("F6"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("D6").Select
    ActiveCell.FormulaR1C1 = "=RC[2]"
    Range("D7").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[2]=R[-1]C[2],"""",RC[2])"
    Range("D7").Select
    Selection.AutoFill Destination:=Range("D7:D150"), Type:=xlFillDefault
    'Range("D7:D150").Select
        Range("F4").Copy
    'Selection.Copy
    Range("D4").Select
    ActiveSheet.Paste
    Columns("F:F").Select
    Selection.EntireColumn.Hidden = True
    Range("R6").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""",IF(RC[-1]="""",1,0),0)"
    Range("R6").Select
    Selection.AutoFill Destination:=Range("R6:R255"), Type:=xlFillDefault
    Range("R6:R255").Select
With ActiveWindow
        .DisplayGridlines = False
        .DisplayZeros = False
    End With
    Range("D6:R6").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$O6=0"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 4
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$R6=1"
    With Selection.FormatConditions(2).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 6
    End With
    Selection.FormatConditions(2).Interior.ColorIndex = 3
    ActiveWindow.SmallScroll ToRight:=8
    Selection.AutoFill Destination:=Range("D6:R339"), Type:=xlFillFormats
Range("D6:Q339").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("D4:Q4").Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 12
        .ColorIndex = 6
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With Selection.Interior
        .ColorIndex = 3
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
Columns("R:R").Select
    Selection.EntireColumn.Hidden = True
    Columns("A:Q").EntireColumn.AutoFit
Columns("B:C").Select
    Selection.EntireColumn.Hidden = True
        Range("F4").Select
    Selection.Copy
    Range("D4").Select
    ActiveSheet.Paste
    Columns("F:F").Select
    Selection.EntireColumn.Hidden = True
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("I2").Select
    Selection.Font.ColorIndex = 5
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Arial"
        .Size = 18
        .ColorIndex = 5
    End With
  Dim myBottom As String
'Set active print range.
'Find bottom of page, set print range.
Sheets("zflexdetails").Range("B1:" & [B65536].End(xlUp).Offset(0, 17).Address).Select
myBottom = Selection.Address
ActiveSheet.PageSetup.PrintArea = myBottom
'Print active range.
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

---

Can anyone show me the correct way to only format as far as Required , Reason being that sheet changes daily size wise (allways same format)


Merc

Which bit are you referring to Merc?

This code, apart from using a hard-coded value and horrible shortcut notation, seems to do eaxctly that

Code:

---

Sheets("zflexdetails").Range("B1:" & [B65536].End(xlUp).Offset(0, 17).Address).Select

---

:hi: xld

on this Vba above i have 3 rows where i have ranges

1)

Code:

---

Range("E6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("E6:Q90").Select
    Selection.Sort Key1:=Range("F6"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

---

TO SORT

2)

Code:

---

Range("D6").Select
    ActiveCell.FormulaR1C1 = "=RC[2]"
    Range("D7").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[2]=R[-1]C[2],"""",RC[2])"
    Range("D7").Select
    Selection.AutoFill Destination:=Range("D7:D150"), Type:=xlFillDefault

---

FIRST FORMULA


3)

Code:

---

Range("R6").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""",IF(RC[-1]="""",1,0),0)"
    Range("R6").Select
    Selection.AutoFill Destination:=Range("R6:R255"), Type:=xlFillDefault
    Range("R6:R255").Select
   
    With ActiveWindow
        .DisplayGridlines = False
        .DisplayZeros = False
    End With
    Range("D6:R6").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$O6=0"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 4
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$R6=1"
    With Selection.FormatConditions(2).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 6
    End With
    Selection.FormatConditions(2).Interior.ColorIndex = 3
    ActiveWindow.SmallScroll ToRight:=8
    Selection.AutoFill Destination:=Range("D6:R339"), Type:=xlFillFormats

---

COND FORMATS

4)

Code:

---

Range("D6:Q339").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

---

FOR BORDERS

i need to only do these ranges on sheet in cells that have data

Im not sure if i have explained properly if you ned more info lmk

Merc

Well one way is to do this sort of thing, using your first example

Code:

---

Set rng = Range("E6")
    Set rng = Range(rng, rng.End(xlDown))  'find the end cell
    Set rng = rng.Resize(, 13)            'extend across to Q
rng.Sort Key1:=Range("F6"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

---

xld cool

can i implement this on the other ranges aswell ?

Merc

Quote:

---

Originally Posted by mercmannick

xld cool

can i implement this on the other ranges aswell ?

Merc

---

Yeah, same principle.

Code:

---

Sub Zflex_For_Customer2222()
Dim LastRow As Long
Dim bottom As Long
Application.ScreenUpdating = False
LastRow = Range("B65536").End(xlUp).Row
bottom = Range("f65536").End(xlUp).Row
Range("E6:Q" & bottom).Sort Key1:=Range("F6"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
Range("D6").FormulaR1C1 = "=RC[2]"
Range("D7").FormulaR1C1 = "=IF(RC[2]=R[-1]C[2],"""",RC[2])"
Range("D7").AutoFill Destination:=Range("D7:D" & LastRow), Type:=xlFillDefault
Range("F4").Copy _
    Destination:=Range("D4")
Columns("F:F").EntireColumn.Hidden = True
Range("R6").FormulaR1C1 = "=IF(RC[-2]="""",IF(RC[-1]="""",1,0),0)"
Range("R6").AutoFill Destination:=Range("R6:R" & LastRow), Type:=xlFillDefault
With ActiveWindow
    .DisplayGridlines = False
    .DisplayZeros = False
End With
With Range("D6:R6")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$O6=0"
    With .FormatConditions(1).Font
        .Bold = True
        .Italic = False
    End With
    .FormatConditions(1).Interior.ColorIndex = 4
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$R6=1"
    With .FormatConditions(2).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 6
    End With
    .FormatConditions(2).Interior.ColorIndex = 3
    .AutoFill Destination:=Range("D6:R" & LastRow), Type:=xlFillFormats
End With
With Range("D6:Q" & LastRow)
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    With .Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With .Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With .Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With .Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With .Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With .Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End With
With Range("D4:Q4")
    With .Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 12
        .ColorIndex = 6
    End With
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    With .Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With .Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With .Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With .Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With .Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
    End With
    With .Interior
        .ColorIndex = 3
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
End With
Columns("R:R").EntireColumn.Hidden = True
Columns("A:Q").EntireColumn.AutoFit
Columns("B:C").EntireColumn.Hidden = True
Range("F4").Copy _
    Destination:=Range("D4")
Columns("F:F").EntireColumn.Hidden = True
With Range("I2")
    .FormulaR1C1 = "=NOW()"
    With .Font
        .ColorIndex = 5
        .Bold = True
        .Name = "Arial"
        .Size = 18
        .ColorIndex = 5
    End With
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

---

can anyone help me with this code........plz

http://www.mrexcel.com/board2/images...s/icon_eek.gif500) {this.width=500;this.alt='Full View';}" border=0> http://www.mrexcel.com/board2/images...on_biggrin.gif500) {this.width=500;this.alt='Full View';}" border=0>

basically apart from the basic formatting(borders and setup of sheet) my conditional format dosent work as it shud


if col o = 0 or blank then highlight green and bold text

if col p = "" and col q = "" then highlight red and bold yellow text

everything works apart from these two bits can someone point me where it is going wrong plz.............

Mercx,

I think this si waht you are asking

Code:

---

With Range("D6:R6")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($O6=0,$O6="""")"
        With .FormatConditions(1).Font
            .Bold = True
            .Italic = False
        End With
        .FormatConditions(1).Interior.ColorIndex = 4
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($P6="""",$Q6="""")"
        With .FormatConditions(2).Font
            .Bold = True
            .Italic = False
            .ColorIndex = 6
        End With
        .FormatConditions(2).Interior.ColorIndex = 3
        .AutoFill Destination:=Range("D6:R" & LastRow), Type:=xlFillFormats
    End With

---

thnx xld :thumb