Replies

Thanks for the help, I’m trying to absorb all this in and decipher it on my own, I’m taking a class on this but it isn’t as in-depth as this…I’m trying this problem just to test myself and push myself to learn more….I have a question….

What exactly does this do: Count To 1 Step -1

First, to all, I appreciate the criticism, this is my 2nd day ever typing macros….in response to Legare’s Comment;

Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

My code works, I just don’t have a variable for column b being blank.

First, to all, I appreciate the criticism, this is my 2nd day ever typing macros….in response to Legare’s Comment;

Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

My code works, I just don’t have a variable for column b being blank.

I was thinking she wanted to delete those without values in column M (companies with no purchases or whatever….in which case I just made a code that would work for me….) the following code would remove up to 500 instances of ‘0’. I’m still just learning, anyone who would like, please nitpick this code:

Function DeleteEmptyRows()
Dim i As Integer
Dim intRowCount As Integer
intRowCount = Range(“B1”).Select
For i = 1 To 500

Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

Dim R As Long
Dim C As Range
Dim Rng As Range

If (ActiveCell) = 0 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If (ActiveCell) = 0 Then
Rng.Rows®.EntireRow.Delete
End If
Next R

Next i

End Function

I was thinking she wanted to delete those without values in column M (companies with no purchases or whatever….in which case I just made a code that would work for me….) the following code would remove up to 500 instances of ‘0’. I’m still just learning, anyone who would like, please nitpick this code:

Function DeleteEmptyRows()
Dim i As Integer
Dim intRowCount As Integer
intRowCount = Range(“B1”).Select
For i = 1 To 500

Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

Dim R As Long
Dim C As Range
Dim Rng As Range

If (ActiveCell) = 0 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If (ActiveCell) = 0 Then
Rng.Rows®.EntireRow.Delete
End If
Next R

Next i

End Function

Try this – give it a second to run through….

Function DeleteLoop()

Range(“M1”).Select
Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

Call DeleteEmptyRows
End Function

Sub DeleteEmptyRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

If (ActiveCell) = 0 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If (ActiveCell) = 0 Then
Rng.Rows®.EntireRow.Delete
End If
Next R

Call DeleteLoop

End Sub

Try this – give it a second to run through….

Function DeleteLoop()

Range(“M1”).Select
Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

Call DeleteEmptyRows
End Function

Sub DeleteEmptyRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

If (ActiveCell) = 0 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If (ActiveCell) = 0 Then
Rng.Rows®.EntireRow.Delete
End If
Next R

Call DeleteLoop

End Sub

*hoping* That was a typo on her part…

*hoping* That was a typo on her part…

If you change range B1 to M1 it will work, but you’d have to keep running the Macro (setting a macro button or something). I don’t know how to loop it, and haven’t the time to figure it out right now, sorry.

If you change range B1 to M1 it will work, but you’d have to keep running the Macro (setting a macro button or something). I don’t know how to loop it, and haven’t the time to figure it out right now, sorry.

I think this is close to what you need…once you post an example I can help some more….
Sub DeleteEmptyRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

Range(“B1”).Select
Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

If (ActiveCell) = 0 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If (ActiveCell) = 0 Then
Rng.Rows®.EntireRow.Delete
End If
Next R

I think this is close to what you need…once you post an example I can help some more….
Sub DeleteEmptyRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

Range(“B1”).Select
Do
If Not (ActiveCell) = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until (ActiveCell) = 0

If (ActiveCell) = 0 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If (ActiveCell) = 0 Then
Rng.Rows®.EntireRow.Delete
End If
Next R

You can’t customize them after opening the customize menu then right-clicking on the button?

You can’t customize them after opening the customize menu then right-clicking on the button?