| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Editing a macro
I want to revise the macro to state if column K is "" and column N is ""
delete the row and end else if K = "" and N is < "" continue. I do not want to copy N if it is blank......just delete the row. Please help. Thanks. 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 Trim(Selection.Value) = "" Then I want to say if K = "" and N = "" sRange = "N" & nSheetRow end loop...otherwise continue 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("A7").Select Rows("6:70").Select Range(Selection, Selection.End(xlDown)).Select Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3, 11), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True Range("A1").Select End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Editing a macro
Don,
Can't get this to work...any other suggestions? "Don Guillett" wrote: something like this sub deleterowsif() for i= cells(rows.count,"k").end(xlup).row to 2 step-1 if cells(i,"k")="" and cells(i,"n")="" then rows(i).delete next i end sub -- Don Guillett SalesAid Software "pm" wrote in message ... I want to revise the macro to state if column K is "" and column N is "" delete the row and end else if K = "" and N is < "" continue. I do not want to copy N if it is blank......just delete the row. Please help. Thanks. 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 Trim(Selection.Value) = "" Then I want to say if K = "" and N = "" sRange = "N" & nSheetRow end loop...otherwise continue 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("A7").Select Rows("6:70").Select Range(Selection, Selection.End(xlDown)).Select Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3, 11), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True Range("A1").Select End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Editing a macro
"Can't get this to work " is not terribly descriptive. copy some
representative rows here or send me your workbook. -- Don Guillett SalesAid Software "pm" wrote in message ... Don, ...any other suggestions? "Don Guillett" wrote: something like this sub deleterowsif() for i= cells(rows.count,"k").end(xlup).row to 2 step-1 if cells(i,"k")="" and cells(i,"n")="" then rows(i).delete next i end sub -- Don Guillett SalesAid Software "pm" wrote in message ... I want to revise the macro to state if column K is "" and column N is "" delete the row and end else if K = "" and N is < "" continue. I do not want to copy N if it is blank......just delete the row. Please help. Thanks. 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 Trim(Selection.Value) = "" Then I want to say if K = "" and N = "" sRange = "N" & nSheetRow end loop...otherwise continue 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("A7").Select Rows("6:70").Select Range(Selection, Selection.End(xlDown)).Select Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3, 11), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True Range("A1").Select End Sub |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Editing a macro
Hi Don,
Would you please reply if you get this message? I'm not receiving todays discussions?????!!!! thanks. "Don Guillett" wrote: something like this sub deleterowsif() for i= cells(rows.count,"k").end(xlup).row to 2 step-1 if cells(i,"k")="" and cells(i,"n")="" then rows(i).delete next i end sub -- Don Guillett SalesAid Software "pm" wrote in message ... I want to revise the macro to state if column K is "" and column N is "" delete the row and end else if K = "" and N is < "" continue. I do not want to copy N if it is blank......just delete the row. Please help. Thanks. 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 Trim(Selection.Value) = "" Then I want to say if K = "" and N = "" sRange = "N" & nSheetRow end loop...otherwise continue 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("A7").Select Rows("6:70").Select Range(Selection, Selection.End(xlDown)).Select Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3, 11), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True Range("A1").Select End Sub |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Editing a macro
got it
-- Don Guillett SalesAid Software "pm" wrote in message ... Hi Don, Would you please reply if you get this message? I'm not receiving todays discussions?????!!!! thanks. "Don Guillett" wrote: something like this sub deleterowsif() for i= cells(rows.count,"k").end(xlup).row to 2 step-1 if cells(i,"k")="" and cells(i,"n")="" then rows(i).delete next i end sub -- Don Guillett SalesAid Software "pm" wrote in message ... I want to revise the macro to state if column K is "" and column N is "" delete the row and end else if K = "" and N is < "" continue. I do not want to copy N if it is blank......just delete the row. Please help. Thanks. 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 Trim(Selection.Value) = "" Then I want to say if K = "" and N = "" sRange = "N" & nSheetRow end loop...otherwise continue 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("A7").Select Rows("6:70").Select Range(Selection, Selection.End(xlDown)).Select Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(3, 11), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True Range("A1").Select End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Editing a macro | Excel Discussion (Misc queries) | |||
| Editing a Macro | Excel Discussion (Misc queries) | |||
| Editing Macro | Excel Discussion (Misc queries) | |||
| Editing a simple macro | Excel Worksheet Functions | |||
| in cell editing macro | Excel Discussion (Misc queries) | |||
Linear Mode
