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

[mercmannick]

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

[Bob Phillips]

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

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

[mercmannick]

xld

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

1)

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)

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)

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)

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

FOR BORDERS

[mercmannick]

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

[Bob Phillips]

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

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

[mercmannick]

xld cool

can i implement this on the other ranges aswell ?

Merc

[Bob Phillips]

xld cool

can i implement this on the other ranges aswell ?

Merc

Yeah, same principle.

[mercmannick]

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

[mercmannick]

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

500) {this.width=500;this.alt='Full View';}" border=0> 500) {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.............

[Bob Phillips]

Mercx,

I think this si waht you are asking

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

[mercmannick]

thnx xld