| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro not recognizing blank lines as blank
In the example below loc has two 4276 rows. I have a macro which deletes the
blank rows, however, it's not recognizing the cell as blank. There's no formula in the cell. In the macro I've formatted the rows to get rid of the formula. Any ideas? Delivery Order Location Empl# Employee Name Mt Order Date Date Loc 1591 ALEXANDER, MARK IL Loc 4276 DODD, KEITH RC 11089616 5/14/07 Loc 4276 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro not recognizing blank lines as blank
As always, post your coding effort for comments.
-- Don Guillett SalesAid Software "pm" wrote in message ... In the example below loc has two 4276 rows. I have a macro which deletes the blank rows, however, it's not recognizing the cell as blank. There's no formula in the cell. In the macro I've formatted the rows to get rid of the formula. Any ideas? Delivery Order Location Empl# Employee Name Mt Order Date Date Loc 1591 ALEXANDER, MARK IL Loc 4276 DODD, KEITH RC 11089616 5/14/07 Loc 4276 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro not recognizing blank lines as blank
"Don Guillett" wrote: As always, post your coding effort for comments. -- Don Guillett SalesAid Software "pm" wrote in message ... In the example below loc has two 4276 rows. I have a macro which deletes the blank rows, however, it's not recognizing the cell as blank. There's no formula in the cell. In the macro I've formatted the rows to get rid of the formula. Any ideas? Delivery Order Location Empl# Employee Name Mt Order Date Date Loc 1591 ALEXANDER, MARK IL Loc 4276 DODD, KEITH RC 11089616 5/14/07 Loc 4276 Range("B8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("N8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("E8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Dim nSheetRow As Long 'Current row being looked at Dim bKeepGoing As Boolean 'Used to determine when to stop bKeepGoing = True nSheetRow = 6 Do While bKeepGoing nSheetRow = nSheetRow + 1 sRange = "K" & nSheetRow Range(sRange).Select If Selection.Value = " " Then sRange = "N" & nSheetRow Range(sRange).Select Selection.Copy nSheetRow = nSheetRow - 1 sRange = "N" & nSheetRow Range(sRange).Select ActiveSheet.Paste nSheetRow = nSheetRow + 1 sRange = "N" & nSheetRow Range(sRange).Select Application.CutCopyMode = False Selection.EntireRow.Delete nSheetRow = nSheetRow - 1 End If sRange = "A" & (nSheetRow + 1) Range(sRange).Select If Selection.Value = "" Then bKeepGoing = False End If Loop Range("A1").Select End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro not recognizing blank lines as blank
THis line
If Selection.Value = " " Then Is not looking for BLANKS. IT's looking for a cell with one space in it. Your code could stand a lot of cleanup. I'm just going to give you a change to this line to find the blanks. if isempty(selection) then "pm" wrote: "Don Guillett" wrote: As always, post your coding effort for comments. -- Don Guillett SalesAid Software "pm" wrote in message ... In the example below loc has two 4276 rows. I have a macro which deletes the blank rows, however, it's not recognizing the cell as blank. There's no formula in the cell. In the macro I've formatted the rows to get rid of the formula. Any ideas? Delivery Order Location Empl# Employee Name Mt Order Date Date Loc 1591 ALEXANDER, MARK IL Loc 4276 DODD, KEITH RC 11089616 5/14/07 Loc 4276 Range("B8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("N8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("E8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Dim nSheetRow As Long 'Current row being looked at Dim bKeepGoing As Boolean 'Used to determine when to stop bKeepGoing = True nSheetRow = 6 Do While bKeepGoing nSheetRow = nSheetRow + 1 sRange = "K" & nSheetRow Range(sRange).Select If Selection.Value = " " Then sRange = "N" & nSheetRow Range(sRange).Select Selection.Copy nSheetRow = nSheetRow - 1 sRange = "N" & nSheetRow Range(sRange).Select ActiveSheet.Paste nSheetRow = nSheetRow + 1 sRange = "N" & nSheetRow Range(sRange).Select Application.CutCopyMode = False Selection.EntireRow.Delete nSheetRow = nSheetRow - 1 End If sRange = "A" & (nSheetRow + 1) Range(sRange).Select If Selection.Value = "" Then bKeepGoing = False End If Loop Range("A1").Select End Sub |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro not recognizing blank lines as blank
If you're looking for a cell that looks blank (may contain multiple spaces):
If trim(Selection.Value) = "" Then pm wrote: "Don Guillett" wrote: As always, post your coding effort for comments. -- Don Guillett SalesAid Software "pm" wrote in message ... In the example below loc has two 4276 rows. I have a macro which deletes the blank rows, however, it's not recognizing the cell as blank. There's no formula in the cell. In the macro I've formatted the rows to get rid of the formula. Any ideas? Delivery Order Location Empl# Employee Name Mt Order Date Date Loc 1591 ALEXANDER, MARK IL Loc 4276 DODD, KEITH RC 11089616 5/14/07 Loc 4276 Range("B8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("N8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("E8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Dim nSheetRow As Long 'Current row being looked at Dim bKeepGoing As Boolean 'Used to determine when to stop bKeepGoing = True nSheetRow = 6 Do While bKeepGoing nSheetRow = nSheetRow + 1 sRange = "K" & nSheetRow Range(sRange).Select If Selection.Value = " " Then sRange = "N" & nSheetRow Range(sRange).Select Selection.Copy nSheetRow = nSheetRow - 1 sRange = "N" & nSheetRow Range(sRange).Select ActiveSheet.Paste nSheetRow = nSheetRow + 1 sRange = "N" & nSheetRow Range(sRange).Select Application.CutCopyMode = False Selection.EntireRow.Delete nSheetRow = nSheetRow - 1 End If sRange = "A" & (nSheetRow + 1) Range(sRange).Select If Selection.Value = "" Then bKeepGoing = False End If Loop Range("A1").Select End Sub -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro not recognizing blank lines as blank
Thanks for your assistance.
Using isempty still doesn't delete the blank lines. The row inititally has a formula in it : =IF(+Input!H2=""," ",+Input!H2) then I format it using: Range("K8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False It will delete the lines if I clear the contents or hit the delete key in the blank row, otherwise it's still not recognizing the row as blank. Thanks. "Barb Reinhardt" wrote: THis line If Selection.Value = " " Then Is not looking for BLANKS. IT's looking for a cell with one space in it. Your code could stand a lot of cleanup. I'm just going to give you a change to this line to find the blanks. if isempty(selection) then "pm" wrote: "Don Guillett" wrote: As always, post your coding effort for comments. -- Don Guillett SalesAid Software "pm" wrote in message ... In the example below loc has two 4276 rows. I have a macro which deletes the blank rows, however, it's not recognizing the cell as blank. There's no formula in the cell. In the macro I've formatted the rows to get rid of the formula. Any ideas? Delivery Order Location Empl# Employee Name Mt Order Date Date Loc 1591 ALEXANDER, MARK IL Loc 4276 DODD, KEITH RC 11089616 5/14/07 Loc 4276 Range("B8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("N8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("E8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Dim nSheetRow As Long 'Current row being looked at Dim bKeepGoing As Boolean 'Used to determine when to stop bKeepGoing = True nSheetRow = 6 Do While bKeepGoing nSheetRow = nSheetRow + 1 sRange = "K" & nSheetRow Range(sRange).Select If Selection.Value = " " Then sRange = "N" & nSheetRow Range(sRange).Select Selection.Copy nSheetRow = nSheetRow - 1 sRange = "N" & nSheetRow Range(sRange).Select ActiveSheet.Paste nSheetRow = nSheetRow + 1 sRange = "N" & nSheetRow Range(sRange).Select Application.CutCopyMode = False Selection.EntireRow.Delete nSheetRow = nSheetRow - 1 End If sRange = "A" & (nSheetRow + 1) Range(sRange).Select If Selection.Value = "" Then bKeepGoing = False End If Loop Range("A1").Select End Sub |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro not recognizing blank lines as blank
Thanks for your assistance.
Using If Trim still doesn't delete the blank lines. The row inititally has a formula in it : =IF(+Input!H2=""," ",+Input!H2) then I format it using: Range("K8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False It will delete the lines if I clear the contents or hit the delete key in the blank row, otherwise it's still not recognizing the row as blank. Thanks. "Dave Peterson" wrote: If you're looking for a cell that looks blank (may contain multiple spaces): If trim(Selection.Value) = "" Then pm wrote: "Don Guillett" wrote: As always, post your coding effort for comments. -- Don Guillett SalesAid Software "pm" wrote in message ... In the example below loc has two 4276 rows. I have a macro which deletes the blank rows, however, it's not recognizing the cell as blank. There's no formula in the cell. In the macro I've formatted the rows to get rid of the formula. Any ideas? Delivery Order Location Empl# Employee Name Mt Order Date Date Loc 1591 ALEXANDER, MARK IL Loc 4276 DODD, KEITH RC 11089616 5/14/07 Loc 4276 Range("B8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("N8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("E8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Dim nSheetRow As Long 'Current row being looked at Dim bKeepGoing As Boolean 'Used to determine when to stop bKeepGoing = True nSheetRow = 6 Do While bKeepGoing nSheetRow = nSheetRow + 1 sRange = "K" & nSheetRow Range(sRange).Select If Selection.Value = " " Then sRange = "N" & nSheetRow Range(sRange).Select Selection.Copy nSheetRow = nSheetRow - 1 sRange = "N" & nSheetRow Range(sRange).Select ActiveSheet.Paste nSheetRow = nSheetRow + 1 sRange = "N" & nSheetRow Range(sRange).Select Application.CutCopyMode = False Selection.EntireRow.Delete nSheetRow = nSheetRow - 1 End If sRange = "A" & (nSheetRow + 1) Range(sRange).Select If Selection.Value = "" Then bKeepGoing = False End If Loop Range("A1").Select End Sub -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro not recognizing blank lines as blank
Dave - - That worked...thanks so much
"Dave Peterson" wrote: First, if the contains a formula, it won't be empty. Second, I'd change that formula (for future needs) to: =IF(Input!H2="","",Input!H2) (the +'s aren't needed and it usually makes life easier if you don't have to test for a single space or multiple spaces.) And if you're looking at a cell that could contain any number of spaces that you want treated as blank, use: if trim(selection.value) = "" then (as long as the selection is a single cell) pm wrote: Thanks for your assistance. Using isempty still doesn't delete the blank lines. The row inititally has a formula in it : =IF(+Input!H2=""," ",+Input!H2) then I format it using: Range("K8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False It will delete the lines if I clear the contents or hit the delete key in the blank row, otherwise it's still not recognizing the row as blank. Thanks. "Barb Reinhardt" wrote: THis line If Selection.Value = " " Then Is not looking for BLANKS. IT's looking for a cell with one space in it. Your code could stand a lot of cleanup. I'm just going to give you a change to this line to find the blanks. if isempty(selection) then "pm" wrote: "Don Guillett" wrote: As always, post your coding effort for comments. -- Don Guillett SalesAid Software "pm" wrote in message ... In the example below loc has two 4276 rows. I have a macro which deletes the blank rows, however, it's not recognizing the cell as blank. There's no formula in the cell. In the macro I've formatted the rows to get rid of the formula. Any ideas? Delivery Order Location Empl# Employee Name Mt Order Date Date Loc 1591 ALEXANDER, MARK IL Loc 4276 DODD, KEITH RC 11089616 5/14/07 Loc 4276 Range("B8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("N8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("E8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Dim nSheetRow As Long 'Current row being looked at Dim bKeepGoing As Boolean 'Used to determine when to stop bKeepGoing = True nSheetRow = 6 Do While bKeepGoing nSheetRow = nSheetRow + 1 sRange = "K" & nSheetRow Range(sRange).Select If Selection.Value = " " Then sRange = "N" & nSheetRow Range(sRange).Select Selection.Copy nSheetRow = nSheetRow - 1 sRange = "N" & nSheetRow Range(sRange).Select ActiveSheet.Paste nSheetRow = nSheetRow + 1 sRange = "N" & nSheetRow Range(sRange).Select Application.CutCopyMode = False Selection.EntireRow.Delete nSheetRow = nSheetRow - 1 End If sRange = "A" & (nSheetRow + 1) Range(sRange).Select If Selection.Value = "" Then bKeepGoing = False End If Loop Range("A1").Select End Sub -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Macro not recognizing blank lines as blank
I still think that trim() still would work. <vbg
pm wrote: Thanks for your assistance. Using If Trim still doesn't delete the blank lines. The row inititally has a formula in it : =IF(+Input!H2=""," ",+Input!H2) then I format it using: Range("K8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False It will delete the lines if I clear the contents or hit the delete key in the blank row, otherwise it's still not recognizing the row as blank. Thanks. "Dave Peterson" wrote: If you're looking for a cell that looks blank (may contain multiple spaces): If trim(Selection.Value) = "" Then pm wrote: "Don Guillett" wrote: As always, post your coding effort for comments. -- Don Guillett SalesAid Software "pm" wrote in message ... In the example below loc has two 4276 rows. I have a macro which deletes the blank rows, however, it's not recognizing the cell as blank. There's no formula in the cell. In the macro I've formatted the rows to get rid of the formula. Any ideas? Delivery Order Location Empl# Employee Name Mt Order Date Date Loc 1591 ALEXANDER, MARK IL Loc 4276 DODD, KEITH RC 11089616 5/14/07 Loc 4276 Range("B8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("N8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Range("E8").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.Paste Application.CutCopyMode = False Dim nSheetRow As Long 'Current row being looked at Dim bKeepGoing As Boolean 'Used to determine when to stop bKeepGoing = True nSheetRow = 6 Do While bKeepGoing nSheetRow = nSheetRow + 1 sRange = "K" & nSheetRow Range(sRange).Select If Selection.Value = " " Then sRange = "N" & nSheetRow Range(sRange).Select Selection.Copy nSheetRow = nSheetRow - 1 sRange = "N" & nSheetRow Range(sRange).Select ActiveSheet.Paste nSheetRow = nSheetRow + 1 sRange = "N" & nSheetRow Range(sRange).Select Application.CutCopyMode = False Selection.EntireRow.Delete nSheetRow = nSheetRow - 1 End If sRange = "A" & (nSheetRow + 1) Range(sRange).Select If Selection.Value = "" Then bKeepGoing = False End If Loop Range("A1").Select End Sub -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete Blank Lines | Excel Discussion (Misc queries) | |||
| a problem with blank lines | Excel Worksheet Functions | |||
| Macro to insert blank lines | Excel Discussion (Misc queries) | |||
| Blank lines in cells | Excel Discussion (Misc queries) | |||
| Delete Blank Lines | Excel Discussion (Misc queries) | |||
Linear Mode
