Welcome Guest
Log In | Register )
You last visited December 3, 2016, 12:35 pm
All times shown are
Eastern Time (GMT-5:00)

Visual Basic Applications in Excel

Topic closed. 19 replies. Last post 11 years ago by lottaloot.

Page 1 of 2
PrintE-mailLink
lottaloot's avatar - AvatarZ56
Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
Posted: December 29, 2005, 7:04 pm - IP Logged
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

 

L ttaL   T

    lottaloot's avatar - AvatarZ56
    Redford/MI
    United States
    Member #3396
    January 18, 2004
    4867 Posts
    Offline
    Posted: December 29, 2005, 7:06 pm - IP Logged

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

    L ttaL   T

      paurths's avatar - underground
      Switching between Fairfax, VA and Belgium
      Belgium
      Member #19287
      July 29, 2005
      2254 Posts
      Offline
      Posted: December 29, 2005, 8:53 pm - IP Logged

      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 - 8ball
        Pennsylvania
        United States
        Member #1340
        April 6, 2003
        2450 Posts
        Offline
        Posted: December 29, 2005, 9:10 pm - IP Logged

        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...

         

        Playing more than one ticket per game is betting against yourself.

          paurths's avatar - underground
          Switching between Fairfax, VA and Belgium
          Belgium
          Member #19287
          July 29, 2005
          2254 Posts
          Offline
          Posted: December 29, 2005, 9:51 pm - IP Logged

          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
            Anna, TX
            United States
            Member #26720
            November 21, 2005
            263 Posts
            Offline
            Posted: December 29, 2005, 9:55 pm - IP Logged

            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
              Redford/MI
              United States
              Member #3396
              January 18, 2004
              4867 Posts
              Offline
              Posted: December 29, 2005, 10:18 pm - IP Logged

              I suppose that is a good idea

              L ttaL   T

                hypersoniq's avatar - 8ball
                Pennsylvania
                United States
                Member #1340
                April 6, 2003
                2450 Posts
                Offline
                Posted: December 30, 2005, 5:58 am - IP Logged

                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 ;-)

                Playing more than one ticket per game is betting against yourself.

                  truecritic's avatar - PirateTreasure
                  Michigan
                  United States
                  Member #22395
                  September 24, 2005
                  1583 Posts
                  Offline
                  Posted: December 30, 2005, 7:25 am - IP Logged

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

                  Smiley Santa

                    lottaloot's avatar - AvatarZ56
                    Redford/MI
                    United States
                    Member #3396
                    January 18, 2004
                    4867 Posts
                    Offline
                    Posted: December 31, 2005, 12:35 pm - IP Logged

                    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


                    L ttaL   T

                      truecritic's avatar - PirateTreasure
                      Michigan
                      United States
                      Member #22395
                      September 24, 2005
                      1583 Posts
                      Offline
                      Posted: December 31, 2005, 12:59 pm - IP Logged

                      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
                        Redford/MI
                        United States
                        Member #3396
                        January 18, 2004
                        4867 Posts
                        Offline
                        Posted: December 31, 2005, 1:14 pm - IP Logged

                        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???  

                        L ttaL   T

                          hypersoniq's avatar - 8ball
                          Pennsylvania
                          United States
                          Member #1340
                          April 6, 2003
                          2450 Posts
                          Offline
                          Posted: December 31, 2005, 1:16 pm - IP Logged

                          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)

                          Playing more than one ticket per game is betting against yourself.

                            lottaloot's avatar - AvatarZ56
                            Redford/MI
                            United States
                            Member #3396
                            January 18, 2004
                            4867 Posts
                            Offline
                            Posted: December 31, 2005, 6:46 pm - IP Logged

                            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  

                            L ttaL   T

                              paurths's avatar - underground
                              Switching between Fairfax, VA and Belgium
                              Belgium
                              Member #19287
                              July 29, 2005
                              2254 Posts
                              Offline
                              Posted: December 31, 2005, 7:02 pm - IP Logged

                              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?