Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto format blank rows using code
Hi
I need to have a spreadsheet searched and where there are complete blank rows I need to colour that row. I can't do this using conditional formatting as there will be data constantly copied into the spreadsheet overwriting any conditional formatting input, therefore it needs to be done using VB code. I already have some code in the workbook (see below) and would like to add to it with the above. I'd appreciate any help. Thanks. Private Sub workbook_open() Sheets("Sheet2").Activate Set f = Columns("A").Find(what:="Date") Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1) If WorksheetFunction.CountA(TestRange.Value) = 0 Then TestRange.EntireRow.Delete End If With Cells ..HorizontalAlignment = xlGeneral ..VerticalAlignment = xlBottom ..WrapText = False ..Orientation = 0 ..AddIndent = False ..IndentLevel = 0 ..ShrinkToFit = False ..ReadingOrder = xlContext ..MergeCells = False ..EntireColumn.AutoFit End With On Error GoTo errhandler ActiveSheet.DrawingObjects.Delete errhandler: Exit Sub End Sub -- Marie Bayes |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto format blank rows using code
Try this small macro:
Sub ColorEmpties() Dim i As Long, j As Long i = Rows.Count For j = 1 To i If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then Rows(j).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student - gsnu200850 "Marie Bayes" wrote: Hi I need to have a spreadsheet searched and where there are complete blank rows I need to colour that row. I can't do this using conditional formatting as there will be data constantly copied into the spreadsheet overwriting any conditional formatting input, therefore it needs to be done using VB code. I already have some code in the workbook (see below) and would like to add to it with the above. I'd appreciate any help. Thanks. Private Sub workbook_open() Sheets("Sheet2").Activate Set f = Columns("A").Find(what:="Date") Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1) If WorksheetFunction.CountA(TestRange.Value) = 0 Then TestRange.EntireRow.Delete End If With Cells .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False .EntireColumn.AutoFit End With On Error GoTo errhandler ActiveSheet.DrawingObjects.Delete errhandler: Exit Sub End Sub -- Marie Bayes |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto format blank rows using code
WOW, that was shockingly bright - however, worked a treat. Are you able to
help me with an amendment - instead of formatting the blank row, how would I find the blank row and then format the row above it instead? Thanks in advance. -- Marie Bayes "Gary''s Student" wrote: Try this small macro: Sub ColorEmpties() Dim i As Long, j As Long i = Rows.Count For j = 1 To i If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then Rows(j).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student - gsnu200850 "Marie Bayes" wrote: Hi I need to have a spreadsheet searched and where there are complete blank rows I need to colour that row. I can't do this using conditional formatting as there will be data constantly copied into the spreadsheet overwriting any conditional formatting input, therefore it needs to be done using VB code. I already have some code in the workbook (see below) and would like to add to it with the above. I'd appreciate any help. Thanks. Private Sub workbook_open() Sheets("Sheet2").Activate Set f = Columns("A").Find(what:="Date") Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1) If WorksheetFunction.CountA(TestRange.Value) = 0 Then TestRange.EntireRow.Delete End If With Cells .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False .EntireColumn.AutoFit End With On Error GoTo errhandler ActiveSheet.DrawingObjects.Delete errhandler: Exit Sub End Sub -- Marie Bayes |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto format blank rows using code
A one line change:
Sub ColorEmpties() Dim i As Long, j As Long i = Rows.Count i = 20 For j = 1 To i If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then Rows(j - 1).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student - gsnu200850 "Marie Bayes" wrote: WOW, that was shockingly bright - however, worked a treat. Are you able to help me with an amendment - instead of formatting the blank row, how would I find the blank row and then format the row above it instead? Thanks in advance. -- Marie Bayes "Gary''s Student" wrote: Try this small macro: Sub ColorEmpties() Dim i As Long, j As Long i = Rows.Count For j = 1 To i If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then Rows(j).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student - gsnu200850 "Marie Bayes" wrote: Hi I need to have a spreadsheet searched and where there are complete blank rows I need to colour that row. I can't do this using conditional formatting as there will be data constantly copied into the spreadsheet overwriting any conditional formatting input, therefore it needs to be done using VB code. I already have some code in the workbook (see below) and would like to add to it with the above. I'd appreciate any help. Thanks. Private Sub workbook_open() Sheets("Sheet2").Activate Set f = Columns("A").Find(what:="Date") Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1) If WorksheetFunction.CountA(TestRange.Value) = 0 Then TestRange.EntireRow.Delete End If With Cells .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False .EntireColumn.AutoFit End With On Error GoTo errhandler ActiveSheet.DrawingObjects.Delete errhandler: Exit Sub End Sub -- Marie Bayes |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto format blank rows using code
Ignore my second post. Use:
Sub ColorEmpties() Dim i As Long, j As Long i = Rows.Count For j = 1 To i If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then Rows(j - 1).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student - gsnu200850 "Marie Bayes" wrote: WOW, that was shockingly bright - however, worked a treat. Are you able to help me with an amendment - instead of formatting the blank row, how would I find the blank row and then format the row above it instead? Thanks in advance. -- Marie Bayes "Gary''s Student" wrote: Try this small macro: Sub ColorEmpties() Dim i As Long, j As Long i = Rows.Count For j = 1 To i If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then Rows(j).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student - gsnu200850 "Marie Bayes" wrote: Hi I need to have a spreadsheet searched and where there are complete blank rows I need to colour that row. I can't do this using conditional formatting as there will be data constantly copied into the spreadsheet overwriting any conditional formatting input, therefore it needs to be done using VB code. I already have some code in the workbook (see below) and would like to add to it with the above. I'd appreciate any help. Thanks. Private Sub workbook_open() Sheets("Sheet2").Activate Set f = Columns("A").Find(what:="Date") Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1) If WorksheetFunction.CountA(TestRange.Value) = 0 Then TestRange.EntireRow.Delete End If With Cells .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False .EntireColumn.AutoFit End With On Error GoTo errhandler ActiveSheet.DrawingObjects.Delete errhandler: Exit Sub End Sub -- Marie Bayes |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto format blank rows using code
Marie,
Change the line Rows(j)..... to this: Rows(j - 1).Interior.ColorIndex = 3 Regards, Per "Marie Bayes" skrev i meddelelsen ... WOW, that was shockingly bright - however, worked a treat. Are you able to help me with an amendment - instead of formatting the blank row, how would I find the blank row and then format the row above it instead? Thanks in advance. -- Marie Bayes "Gary''s Student" wrote: Try this small macro: Sub ColorEmpties() Dim i As Long, j As Long i = Rows.Count For j = 1 To i If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then Rows(j).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student - gsnu200850 "Marie Bayes" wrote: Hi I need to have a spreadsheet searched and where there are complete blank rows I need to colour that row. I can't do this using conditional formatting as there will be data constantly copied into the spreadsheet overwriting any conditional formatting input, therefore it needs to be done using VB code. I already have some code in the workbook (see below) and would like to add to it with the above. I'd appreciate any help. Thanks. Private Sub workbook_open() Sheets("Sheet2").Activate Set f = Columns("A").Find(what:="Date") Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1) If WorksheetFunction.CountA(TestRange.Value) = 0 Then TestRange.EntireRow.Delete End If With Cells .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False .EntireColumn.AutoFit End With On Error GoTo errhandler ActiveSheet.DrawingObjects.Delete errhandler: Exit Sub End Sub -- Marie Bayes |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Auto format blank rows using code
Once again you haven't let me down! Great result, thanks so much.
-- Marie Bayes "Gary''s Student" wrote: Ignore my second post. Use: Sub ColorEmpties() Dim i As Long, j As Long i = Rows.Count For j = 1 To i If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then Rows(j - 1).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student - gsnu200850 "Marie Bayes" wrote: WOW, that was shockingly bright - however, worked a treat. Are you able to help me with an amendment - instead of formatting the blank row, how would I find the blank row and then format the row above it instead? Thanks in advance. -- Marie Bayes "Gary''s Student" wrote: Try this small macro: Sub ColorEmpties() Dim i As Long, j As Long i = Rows.Count For j = 1 To i If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then Rows(j).Interior.ColorIndex = 3 End If Next End Sub -- Gary''s Student - gsnu200850 "Marie Bayes" wrote: Hi I need to have a spreadsheet searched and where there are complete blank rows I need to colour that row. I can't do this using conditional formatting as there will be data constantly copied into the spreadsheet overwriting any conditional formatting input, therefore it needs to be done using VB code. I already have some code in the workbook (see below) and would like to add to it with the above. I'd appreciate any help. Thanks. Private Sub workbook_open() Sheets("Sheet2").Activate Set f = Columns("A").Find(what:="Date") Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1) If WorksheetFunction.CountA(TestRange.Value) = 0 Then TestRange.EntireRow.Delete End If With Cells .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False .EntireColumn.AutoFit End With On Error GoTo errhandler ActiveSheet.DrawingObjects.Delete errhandler: Exit Sub End Sub -- Marie Bayes |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Hide Blank Rows | Excel Discussion (Misc queries) | |||
auto hiding blank rows | Excel Discussion (Misc queries) | |||
VBA code to hide blank rows | Excel Worksheet Functions | |||
Auto transfer rows from 1 sheet to another by account code. | Excel Worksheet Functions | |||
Auto Filter out text or blank rows | Excel Discussion (Misc queries) |