Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro only "Works" with F-8
Here's the Macro:
Sub DeleteBlanksInColumnA() ' ' Macro1 Macro ' Macro recorded 5/17/2007 by Administrator ' Dim rngCell, rngNewCell As Range Dim varName, varCount As Variant varCount = 1 Sheets("Names by Category").Select With Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each rngCell In .Cells If IsEmpty(rngCell(1, 1)) Then rngCell(1, 1).Select Selection.Delete Shift:=xlUp End If Next rngCell End With End Sub When I step through it with the F8 key, it does what I expect it to do. When I turn it loose with Run, it don't do nuffin'! Whutz goin' on here? -- Dave Temping with Staffmark in Rock Hill, SC |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro only "Works" with F-8
Dave,
the macro works perfectly for me no matter how I run it. I assume you expect it to delete empty cells in the used range in column A. Mike Mike "Dave Birley" wrote: Here's the Macro: Sub DeleteBlanksInColumnA() ' ' Macro1 Macro ' Macro recorded 5/17/2007 by Administrator ' Dim rngCell, rngNewCell As Range Dim varName, varCount As Variant varCount = 1 Sheets("Names by Category").Select With Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each rngCell In .Cells If IsEmpty(rngCell(1, 1)) Then rngCell(1, 1).Select Selection.Delete Shift:=xlUp End If Next rngCell End With End Sub When I step through it with the F8 key, it does what I expect it to do. When I turn it loose with Run, it don't do nuffin'! Whutz goin' on here? -- Dave Temping with Staffmark in Rock Hill, SC |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro only "Works" with F-8
Works fine for me when I run it. There isn't a need to select the
cells though. Depending on the size of your data, that could make a slight impact in runtime. Also, you have 3 variables declared that you aren't using. There is probably a reason for this, but thought I'd mention it anyhow. Here is some tweaked code without the cell selection. Sub DeleteBlanksInColumnA() Dim rngCell, rngNewCell As Range Dim varName, varCount As Variant varCount = 1 Sheets("Names by Category").Select With Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each rngCell In .Cells If IsEmpty(rngCell(1, 1)) Then _ rngCell(1, 1).Delete Shift:=xlUp Next rngCell End With End Sub HTH Dave Birley wrote: Here's the Macro: Sub DeleteBlanksInColumnA() ' ' Macro1 Macro ' Macro recorded 5/17/2007 by Administrator ' Dim rngCell, rngNewCell As Range Dim varName, varCount As Variant varCount = 1 Sheets("Names by Category").Select With Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each rngCell In .Cells If IsEmpty(rngCell(1, 1)) Then rngCell(1, 1).Select Selection.Delete Shift:=xlUp End If Next rngCell End With End Sub When I step through it with the F8 key, it does what I expect it to do. When I turn it loose with Run, it don't do nuffin'! Whutz goin' on here? -- Dave Temping with Staffmark in Rock Hill, SC |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro only "Works" with F-8
It works for me unless there are consecutive blank rows in column A. Then
it will miss one of them. Try this: Sub DeleteBlanksInColumnA() Dim rngCell, rngNewCell As Range Dim varName, varCount As Variant varCount = 1 Sheets("Names by Category").Select With Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each rngCell In .Cells If IsEmpty(rngCell(1, 1)) Then setUnion = setUnion & rngCell(1, 1).Address & "," End If Next rngCell End With If Len(setUnion) 0 _ Then setUnion = Left(setUnion, Len(setUnion) - 1) Range(setUnion).Select Selection.Delete shift:=xlUp Else End If End Sub Regards, Paul "Mike H" wrote in message ... Dave, the macro works perfectly for me no matter how I run it. I assume you expect it to delete empty cells in the used range in column A. Mike Mike "Dave Birley" wrote: Here's the Macro: Sub DeleteBlanksInColumnA() ' ' Macro1 Macro ' Macro recorded 5/17/2007 by Administrator ' Dim rngCell, rngNewCell As Range Dim varName, varCount As Variant varCount = 1 Sheets("Names by Category").Select With Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each rngCell In .Cells If IsEmpty(rngCell(1, 1)) Then rngCell(1, 1).Select Selection.Delete Shift:=xlUp End If Next rngCell End With End Sub When I step through it with the F8 key, it does what I expect it to do. When I turn it loose with Run, it don't do nuffin'! Whutz goin' on here? -- Dave Temping with Staffmark in Rock Hill, SC |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro only "Works" with F-8
Odd -- those extra variables were the result of me starting by hacking
another Macro and just stealing its first 10 lines or so, then not being careful with my "housekeeping". As the number of Rows involved was very small (<200), I just held my finger down on the F8 key and did the dirty deed. However I shall hang on to your tweak for future reference. Thanks for the help! -- Dave Temping with Staffmark in Rock Hill, SC "JW" wrote: Works fine for me when I run it. There isn't a need to select the cells though. Depending on the size of your data, that could make a slight impact in runtime. Also, you have 3 variables declared that you aren't using. There is probably a reason for this, but thought I'd mention it anyhow. Here is some tweaked code without the cell selection. Sub DeleteBlanksInColumnA() Dim rngCell, rngNewCell As Range Dim varName, varCount As Variant varCount = 1 Sheets("Names by Category").Select With Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each rngCell In .Cells If IsEmpty(rngCell(1, 1)) Then _ rngCell(1, 1).Delete Shift:=xlUp Next rngCell End With End Sub HTH Dave Birley wrote: Here's the Macro: Sub DeleteBlanksInColumnA() ' ' Macro1 Macro ' Macro recorded 5/17/2007 by Administrator ' Dim rngCell, rngNewCell As Range Dim varName, varCount As Variant varCount = 1 Sheets("Names by Category").Select With Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each rngCell In .Cells If IsEmpty(rngCell(1, 1)) Then rngCell(1, 1).Select Selection.Delete Shift:=xlUp End If Next rngCell End With End Sub When I step through it with the F8 key, it does what I expect it to do. When I turn it loose with Run, it don't do nuffin'! Whutz goin' on here? -- Dave Temping with Staffmark in Rock Hill, SC |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro only "Works" with F-8
Thanks for the input. Programming can be a lot like genealogy -- negative
information ("it works for me") is just as important as the positive. -- Dave Temping with Staffmark in Rock Hill, SC "Mike H" wrote: Dave, the macro works perfectly for me no matter how I run it. I assume you expect it to delete empty cells in the used range in column A. Mike Mike "Dave Birley" wrote: Here's the Macro: Sub DeleteBlanksInColumnA() ' ' Macro1 Macro ' Macro recorded 5/17/2007 by Administrator ' Dim rngCell, rngNewCell As Range Dim varName, varCount As Variant varCount = 1 Sheets("Names by Category").Select With Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each rngCell In .Cells If IsEmpty(rngCell(1, 1)) Then rngCell(1, 1).Select Selection.Delete Shift:=xlUp End If Next rngCell End With End Sub When I step through it with the F8 key, it does what I expect it to do. When I turn it loose with Run, it don't do nuffin'! Whutz goin' on here? -- Dave Temping with Staffmark in Rock Hill, SC |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro only "Works" with F-8
«It works for me unless there are consecutive blank rows in column A.»
Yup -- that was it! Thanks for catching it. -- Dave Temping with Staffmark in Rock Hill, SC "PCLIVE" wrote: It works for me unless there are consecutive blank rows in column A. Then it will miss one of them. Try this: Sub DeleteBlanksInColumnA() Dim rngCell, rngNewCell As Range Dim varName, varCount As Variant varCount = 1 Sheets("Names by Category").Select With Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each rngCell In .Cells If IsEmpty(rngCell(1, 1)) Then setUnion = setUnion & rngCell(1, 1).Address & "," End If Next rngCell End With If Len(setUnion) 0 _ Then setUnion = Left(setUnion, Len(setUnion) - 1) Range(setUnion).Select Selection.Delete shift:=xlUp Else End If End Sub Regards, Paul "Mike H" wrote in message ... Dave, the macro works perfectly for me no matter how I run it. I assume you expect it to delete empty cells in the used range in column A. Mike Mike "Dave Birley" wrote: Here's the Macro: Sub DeleteBlanksInColumnA() ' ' Macro1 Macro ' Macro recorded 5/17/2007 by Administrator ' Dim rngCell, rngNewCell As Range Dim varName, varCount As Variant varCount = 1 Sheets("Names by Category").Select With Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each rngCell In .Cells If IsEmpty(rngCell(1, 1)) Then rngCell(1, 1).Select Selection.Delete Shift:=xlUp End If Next rngCell End With End Sub When I step through it with the F8 key, it does what I expect it to do. When I turn it loose with Run, it don't do nuffin'! Whutz goin' on here? -- Dave Temping with Staffmark in Rock Hill, SC |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro only "Works" with F-8
It usually makes life a lot simpler to process the data from the bottom row to
the top row. Option Explicit Sub testme01() Dim FirstRow as long dim LastRow as long dim iRow as long with worksheets("names by category") firstrow = 2 lastrow = .cells(.rows.count,"A").end(xlup).row for irow = lastrow to firstrow step -1 if isempty(.cells(irow,"A").value) then .cells(irow,"A").delete shift:=xlup 'or delete the entire row '.rows(irow).delete end if next irow end with end sub ======== Alternatively, you could do this: Manually: select the range (A2:Axx) edit|goto|special|blanks edit|delete|Shift cells up In code: Option Explicit Sub testme02() With Worksheets("names by category") On Error Resume Next .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp On Error GoTo 0 End With End Sub To delete the entire row, use this: .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeBlanks).entirerow.de lete As an aside, if you have empty cells in column A after the last used cell in column A, then those cells/rows won't be deleted with any of this code. You'd have to find the lastrow some other way than relying on column A. Dave Birley wrote: Here's the Macro: Sub DeleteBlanksInColumnA() ' ' Macro1 Macro ' Macro recorded 5/17/2007 by Administrator ' Dim rngCell, rngNewCell As Range Dim varName, varCount As Variant varCount = 1 Sheets("Names by Category").Select With Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each rngCell In .Cells If IsEmpty(rngCell(1, 1)) Then rngCell(1, 1).Select Selection.Delete Shift:=xlUp End If Next rngCell End With End Sub When I step through it with the F8 key, it does what I expect it to do. When I turn it loose with Run, it don't do nuffin'! Whutz goin' on here? -- Dave Temping with Staffmark in Rock Hill, SC -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro only "Works" with F-8
Once again I become very conscious of the fact that, although I understand
the basic (no pun intended) principles of programming, their translation into different languages is as complex as when I am trying to translate from Swahili to French, with English as my native tongue! Having an "interpreter" handy is most helpful <g! Thanks. -- Dave Temping with Staffmark in Rock Hill, SC "Dave Peterson" wrote: It usually makes life a lot simpler to process the data from the bottom row to the top row. Option Explicit Sub testme01() Dim FirstRow as long dim LastRow as long dim iRow as long with worksheets("names by category") firstrow = 2 lastrow = .cells(.rows.count,"A").end(xlup).row for irow = lastrow to firstrow step -1 if isempty(.cells(irow,"A").value) then .cells(irow,"A").delete shift:=xlup 'or delete the entire row '.rows(irow).delete end if next irow end with end sub ======== Alternatively, you could do this: Manually: select the range (A2:Axx) edit|goto|special|blanks edit|delete|Shift cells up In code: Option Explicit Sub testme02() With Worksheets("names by category") On Error Resume Next .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp On Error GoTo 0 End With End Sub To delete the entire row, use this: .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) _ .Cells.SpecialCells(xlCellTypeBlanks).entirerow.de lete As an aside, if you have empty cells in column A after the last used cell in column A, then those cells/rows won't be deleted with any of this code. You'd have to find the lastrow some other way than relying on column A. Dave Birley wrote: Here's the Macro: Sub DeleteBlanksInColumnA() ' ' Macro1 Macro ' Macro recorded 5/17/2007 by Administrator ' Dim rngCell, rngNewCell As Range Dim varName, varCount As Variant varCount = 1 Sheets("Names by Category").Select With Range("A2", Range("A" & Rows.Count).End(xlUp)) For Each rngCell In .Cells If IsEmpty(rngCell(1, 1)) Then rngCell(1, 1).Select Selection.Delete Shift:=xlUp End If Next rngCell End With End Sub When I step through it with the F8 key, it does what I expect it to do. When I turn it loose with Run, it don't do nuffin'! Whutz goin' on here? -- Dave Temping with Staffmark in Rock Hill, SC -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Read ".dll" files, the Macro "work flow" and the actual values of the variables when Macro is running | Excel Programming | |||
Change default in input box from "20" to "000000"? Have macro continue on to name sheet tab? | Excel Programming | |||
Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A" | Excel Discussion (Misc queries) | |||
Sending macro based e-mail with built-in "Heading" and "Text" | Excel Programming |