PDA

View Full Version : Running Code in Visual Basic Window Versus in Excel



jdautel
09-20-2016, 12:48 PM
Here's a simple piece of code I wrote to delete all rows in my spreadsheet that had the value N in the A cell. When I run it from the VBA window it only does one iteration of the for loop (i.e.) only deleting one row. When I run it from the excel screen with the macro it deletes ALL the rows with the value N in their A cell. Why does it only run one iteration in the visual basic window?


Option Explicit

Sub deletemorerows()

Dim cell As Range
For Each cell In Range("A2:A2000")
If cell.Value = "N" Then
cell.EntireRow.Delete
End If
Next cell

End Sub


Also, I understand that I could just sort and delete all the N values; however, I try to use VBA whenever I can to get more and more familiar with it.

Thanks in advance for any responses!

SamT
09-20-2016, 01:25 PM
Delete from bottom to top


Sub VBAX_SamT()
Dim R as Long

For R = Cells(Rows.Count, "A").End(xlUp).Row to 1 Step -1
If Cells(R, "A") = "N" Then Rows(R).Delete
Next R

End Sub

jdautel
09-20-2016, 01:52 PM
Was it skipping rows because it was deleting them as it went down the list?

Paul_Hossler
09-20-2016, 02:05 PM
Yes

Here's another way that does not require looping that (depending on the data) might be faster



Option Explicit

Sub AnotherWay()
With Range("A2:A2000")
Call .Replace("N", True, xlWhole, , True)
On Error Resume Next
.SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
On Error GoTo 0
End With

End Sub

SamT
09-20-2016, 03:06 PM
Neat

jdautel
09-21-2016, 06:24 AM
Just curious, how long have y'all been using VBA? I've been using it for about two months now and been able to automate some pretty time consuming tasks; however, I want to get a lot better at it. Do you know of any good courses on it or is it just something you have to learn over time by doing?

jdautel
09-21-2016, 06:38 AM
Also is there a way to configure your settings in here to notify you when there's a new thread posted? That way I can scan my email and see if there's any new threads of interest

SamT
09-21-2016, 06:53 AM
At the top of the VBAX Window Click on Settings

In My Settings, Click General Settings

At Default Thread Subscription Mode:, select email in dropdown box