Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have code working but need to edit to use on multiple variable len
Have code working but need to edit to use on multiple variable length
worksheets instead of just applying the lines to a selected range. I have probably 100 sheets that all need the same formatting but the sheets do not all contain the same number of records. This will work for the selected sheets but if I don't give the ending cell I get an error. Sub Macro3() range("b4:b42,c4:c42,e4:e42,f4:f42,h4:h42,i4:i42,k 6:k42,l6:l42").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With range("G4:G45, J4:J45").Select With Selection.Borders(xlEdgeRight) .LineStyle = xlDashDot .Weight = xlMedium .ColorIndex = xlAutomatic End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have code working but need to edit to use on multiple variable len
you can get the last used cell in a particular column with
lastcell = ActiveSheet.Cells(Rows.Count, "i").End(xlUp).Row change the i to the column you want the link of, then change your formulas from h2:h42 to "h2:h" & lastcell -- -John Please rate when your question is answered to help us and others know what is helpful. "jeremiah" wrote: Have code working but need to edit to use on multiple variable length worksheets instead of just applying the lines to a selected range. I have probably 100 sheets that all need the same formatting but the sheets do not all contain the same number of records. This will work for the selected sheets but if I don't give the ending cell I get an error. Sub Macro3() range("b4:b42,c4:c42,e4:e42,f4:f42,h4:h42,i4:i42,k 6:k42,l6:l42").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With range("G4:G45, J4:J45").Select With Selection.Borders(xlEdgeRight) .LineStyle = xlDashDot .Weight = xlMedium .ColorIndex = xlAutomatic End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have code working but need to edit to use on multiple variable
When I leave this as Option Explicit I get the error variable not assigned
for lastcell. If I move it to public then I get an invalid use of property error when it gets to the range ("a1:A").select. Is there something I need to define, not good at this part of coding at all. "John Bundy" wrote: you can get the last used cell in a particular column with lastcell = ActiveSheet.Cells(Rows.Count, "i").End(xlUp).Row change the i to the column you want the link of, then change your formulas from h2:h42 to "h2:h" & lastcell -- -John Please rate when your question is answered to help us and others know what is helpful. "jeremiah" wrote: Have code working but need to edit to use on multiple variable length worksheets instead of just applying the lines to a selected range. I have probably 100 sheets that all need the same formatting but the sheets do not all contain the same number of records. This will work for the selected sheets but if I don't give the ending cell I get an error. Sub Macro3() range("b4:b42,c4:c42,e4:e42,f4:f42,h4:h42,i4:i42,k 6:k42,l6:l42").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With range("G4:G45, J4:J45").Select With Selection.Borders(xlEdgeRight) .LineStyle = xlDashDot .Weight = xlMedium .ColorIndex = xlAutomatic End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have code working but need to edit to use on multiple variable
With option explicit on, which is almost always the best, you need to tell
the program what kind of variable you are using, try adding this at the top just after the Sub line Dim lastCell as Integer this tells the program that it should expect a whole number, which row values are -- -John Please rate when your question is answered to help us and others know what is helpful. "jeremiah" wrote: When I leave this as Option Explicit I get the error variable not assigned for lastcell. If I move it to public then I get an invalid use of property error when it gets to the range ("a1:A").select. Is there something I need to define, not good at this part of coding at all. "John Bundy" wrote: you can get the last used cell in a particular column with lastcell = ActiveSheet.Cells(Rows.Count, "i").End(xlUp).Row change the i to the column you want the link of, then change your formulas from h2:h42 to "h2:h" & lastcell -- -John Please rate when your question is answered to help us and others know what is helpful. "jeremiah" wrote: Have code working but need to edit to use on multiple variable length worksheets instead of just applying the lines to a selected range. I have probably 100 sheets that all need the same formatting but the sheets do not all contain the same number of records. This will work for the selected sheets but if I don't give the ending cell I get an error. Sub Macro3() range("b4:b42,c4:c42,e4:e42,f4:f42,h4:h42,i4:i42,k 6:k42,l6:l42").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With range("G4:G45, J4:J45").Select With Selection.Borders(xlEdgeRight) .LineStyle = xlDashDot .Weight = xlMedium .ColorIndex = xlAutomatic End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have code working but need to edit to use on multiple variable len
How about something like:
Option Explicit Sub Macro3A() Dim wks As Worksheet Dim LastRow As Long Dim myRng As Range Dim myCol As Range For Each wks In ActiveWorkbook.Worksheets With wks 'using column A to get the last row LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set myRng = Intersect(.Range("b:c,e:f,h:i,k:l"), _ .Rows("4:" & LastRow)) For Each myCol In myRng.Columns With myCol .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone With .Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End With Next myCol Set myRng = Intersect(.Range("G:g,J:J"), .Rows("4:" & LastRow)) For Each myCol In myRng.Columns With myCol.Borders(xlEdgeRight) .LineStyle = xlDashDot .Weight = xlMedium .ColorIndex = xlAutomatic End With Next myCol 'did you really mean to exclude K4:L5 in the first portion? Set myRng = .Range("K4:L5") For Each myCol In myRng.Columns With myCol.Borders(xlEdgeRight) .LineStyle = xlNone End With Next myCol End With Next wks End Sub jeremiah wrote: Have code working but need to edit to use on multiple variable length worksheets instead of just applying the lines to a selected range. I have probably 100 sheets that all need the same formatting but the sheets do not all contain the same number of records. This will work for the selected sheets but if I don't give the ending cell I get an error. Sub Macro3() range("b4:b42,c4:c42,e4:e42,f4:f42,h4:h42,i4:i42,k 6:k42,l6:l42").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With range("G4:G45, J4:J45").Select With Selection.Borders(xlEdgeRight) .LineStyle = xlDashDot .Weight = xlMedium .ColorIndex = xlAutomatic End With End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Have code working but need to edit to use on multiple variable
Actually, no I didn't mean to leave out those rows in K and L. Thanks. I
was initially trying to format all my worksheets at once but realized after I started that not all of them have the same layout, so I will adjust this to work on the selected sheets, not all sheets. Thanks again for your help. "Dave Peterson" wrote: How about something like: Option Explicit Sub Macro3A() Dim wks As Worksheet Dim LastRow As Long Dim myRng As Range Dim myCol As Range For Each wks In ActiveWorkbook.Worksheets With wks 'using column A to get the last row LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set myRng = Intersect(.Range("b:c,e:f,h:i,k:l"), _ .Rows("4:" & LastRow)) For Each myCol In myRng.Columns With myCol .Borders(xlDiagonalDown).LineStyle = xlNone .Borders(xlDiagonalUp).LineStyle = xlNone With .Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End With Next myCol Set myRng = Intersect(.Range("G:g,J:J"), .Rows("4:" & LastRow)) For Each myCol In myRng.Columns With myCol.Borders(xlEdgeRight) .LineStyle = xlDashDot .Weight = xlMedium .ColorIndex = xlAutomatic End With Next myCol 'did you really mean to exclude K4:L5 in the first portion? Set myRng = .Range("K4:L5") For Each myCol In myRng.Columns With myCol.Borders(xlEdgeRight) .LineStyle = xlNone End With Next myCol End With Next wks End Sub jeremiah wrote: Have code working but need to edit to use on multiple variable length worksheets instead of just applying the lines to a selected range. I have probably 100 sheets that all need the same formatting but the sheets do not all contain the same number of records. This will work for the selected sheets but if I don't give the ending cell I get an error. Sub Macro3() range("b4:b42,c4:c42,e4:e42,f4:f42,h4:h42,i4:i42,k 6:k42,l6:l42").Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With range("G4:G45, J4:J45").Select With Selection.Borders(xlEdgeRight) .LineStyle = xlDashDot .Weight = xlMedium .ColorIndex = xlAutomatic End With End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit Menu shortcut not fully working | Excel Discussion (Misc queries) | |||
Winzip and email multiple files (RondeBriuins code not working) | Excel Programming | |||
How to Create a Macro to Edit a Variable Amount of Information | New Users to Excel | |||
Edit single character within a variable | Excel Programming | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions |