PDA

View Full Version : [SOLVED] How to make the range only as long as needs to be ...



mercmannick
07-15-2005, 10:12 AM
:banghead:


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
07-15-2005, 10:36 AM
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
07-15-2005, 10:45 AM
:hi: 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
07-15-2005, 10:46 AM
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
07-15-2005, 11:31 AM
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
07-15-2005, 11:48 AM
xld cool

can i implement this on the other ranges aswell ?

Merc

Bob Phillips
07-15-2005, 02:01 PM
xld cool

can i implement this on the other ranges aswell ?

Merc

Yeah, same principle.

mercmannick
07-16-2005, 09:21 AM
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
07-16-2005, 09:22 AM
can anyone help me with this code........plz

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

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
07-16-2005, 06:04 PM
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
07-17-2005, 02:23 PM
thnx xld :thumb