Adjusting code

[cmpgeek]

i found the following coding in the KB... it is not quite what i was hoping for, but works great!... what i am wondering is if there is a way to adjust it. The code is for removing rows in an Excel spreadsheet depending on user input... DRJ wrote it (thanks DRJ!)...


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Column = 3 Then
Exit Sub
End If
If Target(1, 1).Value = "d" Then
Target(1, 1).EntireRow.Delete
End If
End Sub

what i am wondering is if it is possible to adjust this coding so that i can give it a range of rows to look at, and have it remove all rows where there is nothing in "cell A"... does that make sense?

what my problem is, is that i am exporting report results from Crystal into Excel and without fail - there is always at least 1 or 2 empty rows between each detail line. if there were only 20 or so lines, it would not be a big deal, but when i am dealing with 6+ pages of lines, it gets to be a bit more of a PIA...

the coding DRJ wrote is absolutely wonderful, i am just wondering if the other idea is possible...

thanks for any suggestions yall may have...

[Ken Puls]

Hi there,

I'd probably go with this, but not in a worksheet change event... that could get nasty! Try dropping it in a standard module (and commenting the worksheet change event before you run it.)


Sub test()
Dim x As Long
With ActiveSheet
For x = .Range("A65536").End(xlUp).Row To 1 Step -1
If .Range("A" & x).Value = "" Then .Range("A" & x).EntireRow.Delete
Next x
End With
End Sub

HTH,

[Zack Barresse]

Moved to the Excel Help forum. :)

[cmpgeek]

Moved to the Excel Help forum. :)
thanks firefytr i had debated whether or not to put it there several times, i decided to place it in the KB related one since i was pulling the code from there and figured id rather be safe than sorry if that makes any sense...

[cmpgeek]

Hi there,

I'd probably go with this, but not in a worksheet change event... that could get nasty! Try dropping it in a standard module (and commenting the worksheet change event before you run it.)
HTH,
thanks for your suggestion Ken, but i do not understand what you mean by the above comment...

i apologize for being simple; but this is exactly how i am learning VBA - one post at a time lol... (hence the "resident newbie" comment & :bink: lol)

thanks again

[Ken Puls]

Hi there,

The code you posted originally gets placed in a worksheet module, and will run every time something changes on the worksheet. If you're not careful, you can create loops because you fire an event, which triggers the code, which adjusts the sheet... which fires an event, which triggers the code... see what I mean? <shudder>

If you go into VBA and right click on your project, choose insert module, that's where my code should go. You then run it by pressing Alt+F8, then choosing it for the list.

I have to run now, but let me know if you need more asssistance.

Cheers,

[NateO]

Hello,

In this case, I'd be tempted to use an Autofilter, e.g.,



Sub DellTime()
Dim myRng As Range
Application.ScreenUpdating = False
On Error Resume Next
With Sheets(1)
.AutoFilterMode = False
Set myRng = Range(.Cells(2, 1), .Cells(65336, 1).End(xlUp))
myRng.AutoFilter Field:=1, Criteria1:="="
myRng.SpecialCells(xlVisible).EntireRow.Delete
.AutoFilterMode = False
With .Range("a1")
If Not CBool(Len(.Value)) Then .EntireRow.Delete
End With
End With
Set myRng = Nothing
Application.ScreenUpdating = True
End Sub

[cmpgeek]

OMG - that is absolutely wonderful!!!!!!

that will save me SO much time on some of this stuff... TYTYTYTYTY
:ipray:

[cmpgeek]

Nate i just saw your suggestion... i re-exported the information and tried your solution as well... they both worked WONDERFULLY, but yours does do it a lot faster... thank you for offering your help as well!

yall are wonderful!

[NateO]

You are welcome.

Nate i just saw your suggestion... i re-exported the information and tried your solution as well... they both worked WONDERFULLY, but yours does do it a lot faster... The performance implications are the root of my aforementioned temptation. http://www.vbaexpress.com/forum/images/icons/icon10.gif

Also, while I'm posting here, the following:

= ""

In a loop (anywhere actually, but more so in a loop) is far more expensive than:

= vbNullstring

[Ken Puls]

Hi CM Nate!

(Congrats, by the way)

I like the autofilter method... proving once again that there are always more than 3 ways to do anything Excel! :)

Appreciate the lesson on the vbNullString, too. I was not aware of that... think I might have to change some of my coding!:eek:

Cheers,

[NateO]

Hello Ken, thanks. :)

Re: vbNullstring (more so in a loop). Yeah, if you put your timer to work you can see it. I ran the following procedure 40 times for the two types of assignment:


Public Declare Function QueryPerformanceFrequency _
Lib "kernel32.dll" ( _
lpFrequency As Currency) As Long
Public Declare Function QueryPerformanceCounter _
Lib "kernel32.dll" ( _
lpPerformanceCount As Currency) As Long
Private Sub Time_Addition()
Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
Dim Overhead As Currency, A As String, I As Long
QueryPerformanceFrequency Freq
QueryPerformanceCounter Ctr1
QueryPerformanceCounter Ctr2
Overhead = Ctr2 - Ctr1 ' determine API overhead
QueryPerformanceCounter Ctr1 ' time loop
For I = 1 To 10000
'A = ""
A = vbNullString
Next I
QueryPerformanceCounter Ctr2
Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq
End Sub

A = "" averaged 0.00167 seconds
A = vbNullstring averaged 0.00034 seconds

A pretty healthy jump at a 379% Variance.

While vbNullstring is not exactly "", it pretty much functions that way for the most part in VB. I probably could have used it for my Filter criteria...

The following has some worthwile commentary on String Optimization in Visual Basic if you're looking for some light reading on the subject:
http://www.aivosto.com/vbtips/stringopt.html

I'm not sure I agree with all of this, like:


If LenB(Text$) <> 0 Then

Looks like:


If Cbool(LenB(Text$)) Then

To me, but, it's still worth a gander.

Have a nice evening.

[Ken Puls]

Hi Nate!

Cool stuff there... I got values of:
A = "" of .00291 seconds
A = vbnullstring of .00091 seconds
Older PC, I'm sure, but still a 320% difference.

Re the article, does all that stuff work in VBA? (Left$ vs Left, etc...) I'm curious if you know off the top of your head if it's VB6 specific or not.

I'm going to need to read through it a few more times, I think!

Appreciate the info!

[NateO]

Re the article, does all that stuff work in VBA? (Left$ vs Left, etc...) I'm curious if you know off the top of your head if it's VB6 specific or not.It's true for both Ken.


MsgBox Right$("functional?", 1)

But again, I'm on board with pieces of the article, but not 100%.

Also in your VBE, click Help->About Microsoft Visual Basic... :)