Share

Visual Basic Applications in Excel

lottaloot's avatar - AvatarZ56
Found this interesting excel VBA at http://www.ozgrid.com/VBA/VBACode.htm  Thought someone might find a use for it. 

Sub DeleteBlankRows1()
'Deletes the entire row within the selection if the ENTIRE row contains no data.

'We use Long in case they have over 32,767 rows selected.
Dim i As Long

    'We turn off calculation and screenupdating to speed up the macro.
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
     
    'We work backwards because we are deleting rows.
    For i = Selection.Rows.Count To 1 Step -1
        If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
            Selection.Rows(i).EntireRow.Delete
        End If
    Next i

        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

 

lottaloot's avatar - AvatarZ56

You can actually delete the "we" statements as well as the statement below the sub procedure starting with "delete"

paurths's avatar - underground

anything starting with    '    is considered as comment in VB, and will not be executed. (probably the wrong word... translation from dutch... sorry)

hypersoniq's avatar - binary

not a good idea to delete comments

actually you should add your own comment lines so in 2 years you remember WHY you used a particular piece of code.

trust me on that...

 

paurths's avatar - underground

not a good idea to delete comments

actually you should add your own comment lines so in 2 years you remember WHY you used a particular piece of code.

trust me on that...

 

Why was that again? Confused

 LOL Wink

Avatar

not a good idea to delete comments

actually you should add your own comment lines so in 2 years you remember WHY you used a particular piece of code.

trust me on that...

 

Why was that again? Confused

 LOL Wink

So you don't execute the wrong per...Peo...I mean code.

lottaloot's avatar - AvatarZ56

I suppose that is a good idea

hypersoniq's avatar - binary

Sub DeleteBlankRows1()

Dim i As Long

  .Calculation = xlCalculationManual
  .ScreenUpdating = False

    For i = Selection.Rows.Count To 1 Step -1
      If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
       Selection.Rows(i).EntireRow.Delete
      End If
    Next i

      .Calculation = xlCalculationAutomatic
      .ScreenUpdating = True
    End With
End Sub

The first version is easier to understand because of the comments, even though both accomplish the same task... commenting is just a good programming practice ;-)

truecritic's avatar - PirateTreasure

Problem is...2 yrs later and my comments don't even make sense to me.

Smiley Santa

lottaloot's avatar - AvatarZ56

Does anyone know what this is actually saying???

I know you are adding rows here. 

But what does the dimming mean??

 

Sub AddRows()
Dim sh As Worksheet
Dim i As Long


truecritic's avatar - PirateTreasure

DIM is Dimension.  Used for a variable as an array.  Would assume the same applies to Excel.  I don't program Excel, so I'll let the experts provide more details.

lottaloot's avatar - AvatarZ56

Does anyone know what this is actually saying???

I know you are adding rows here. 

But what does the dimming mean??

 

Sub AddRows()
Dim sh As Worksheet
Dim i As Long


So dim sh as worksheet is saying choose the entire worksheet???

& dim i as long is saying that that the interger should be long (or expecting a bigger number than usual???  

hypersoniq's avatar - binary

Does anyone know what this is actually saying???

I know you are adding rows here. 

But what does the dimming mean??

 

Sub AddRows()
Dim sh As Worksheet
Dim i As Long


Sub AddRows()  ' a subroutine that can be referenced by the name AddRows

Dim sh As Worksheet  ' dim=dimension, used to declare a variable (sh) AS a data-type/object (Worksheet)

Dim i As Long  'declare variable (i) as long data type (long = Long Integer, used when Integer is too small, values of Long data type are 32 bits where INT holds 16 bits)

lottaloot's avatar - AvatarZ56

Lookup function in excel

Ok guys, here's the deal.  I am seriously wanting to locate the DATE that an overdue combo was last seen in my spreadsheet. 

I read about vlookup & hlookup and I am pretty sure that this is what I want to use. 

What I need is an interpretation of this 

Hlookup=  address(row_num,column,abs_num,a1,sheet_text

Vlookup= (lookup_value_table_array,col,index_num,...)

This is what I do not get  (what exactly goes in each of these little boxes) 

This is why I can not go any further with excel  (I never know what goes in the little boxes) Confused  

paurths's avatar - underground

Lookup function in excel

Ok guys, here's the deal.  I am seriously wanting to locate the DATE that an overdue combo was last seen in my spreadsheet.

I read about vlookup & hlookup and I am pretty sure that this is what I want to use.

What I need is an interpretation of this

Hlookup=address(row_num,column,abs_num,a1,sheet_text

Vlookup= (lookup_value_table_array,col,index_num,...)

This is what I do not get  (what exactly goes in each of these little boxes) 

This is why I can not go any further with excel (I never know what goes in the little boxes) Confused

Hi Lottaloot,
i'm not an excel expert, but i guess this explanation will do... (?)
A              B
67     1
35     2
32     3
36     4
45     5
36     6
48     7
23     8
22     9
66     10
88     11

I have selected these two columns and named it "Draws".
This goes for Vertical lookup: =VERT.ZOEKEN(66;Draws;2)
I guess "VERT.ZOEKEN" is the same as VLookup
What it does: look for the value 66 in "Draws", when it finds this, it returns the value of the second column, on the same row, which in this case is 10.

Does it make sense?

Welcome Guest

Your last visit: Sun, Oct 25, 2020, 11:48 am

Log In

Log InCancel

Forgot your username?

Forgot your password?