• Auto Insert and Column delete (Office XP )

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Auto Insert and Column delete (Office XP )

    Author
    Topic
    #421435

    Hi Hans and all,

    Refer to my earlier post which state that :

    Can we write code for the auto insert of empty cells with reference to another cell? ie if some of column B’s cells are blank and I would like to input Abbr. or a 2-characters eg. CA into them by looking at the content in the cells of column D , eg California.? an Example in the file would be Belgium in Column D (Country) and Abbr ( column A) would return BE and so on.

    Is there another way to delete column automatically instead of using the recording marco as it is static and does not change if the new worksheet change its content to another column?

    I have attach a sample for your reference. The column to be delete are column B (address) and Delivery date ( column E). It would be delete before insert the Abbr. describe above.

    Your help and suggestions is very much appreciate.

    btw, if I program it in my home PC which installed Office XP, can I use the program in my office which run Excel 2000?

    Thanks , kun

    Viewing 1 reply thread
    Author
    Replies
    • #957590

      I would use a separate table holding country names and abbreviations, and VLOOKUP formulas in column A. In the attached version, the table is in columns A:B on Sheet2. The formula in B2 is

      =VLOOKUP(F2,Sheet2!$A:$B,2,FALSE)

      and this is filled down as far as needed.

      BTW: wouldn’t it be easier to enter the abbreviation and let the formula fill in the complete name?

      • #957654

        Hans,
        Thank for replying, I am given the complete name and I want the abbreviation.
        Can’t download your file, will look at it tonite.

        thanks, kun

      • #957708

        Thanks alot Hans, it is excellent!

        Can the formula be protected or better invisible, some user might accidentaly delete it. Can you also explain what is the HLOOKUP and DLOOKUP for and when I should be using it..

        Thanks Always, kun

        • #957713

          Cell are “locked” by default. Goto format – cells -protection (tab) and check “Hidden” to make them hidden.
          The select any cells that you want to be “editable” by the user:
          Goto format – cells -protection (tab) and uncheck “Locked”
          Now Protect the worksheet
          Tools – protection – protect sheet (set a password if desired)

          Steve

        • #957714

          You can protect cells and hide formulas, but only as part of a protected worksheet. The procedure is as follows:
          – Select the cells that the user should be able to edit (NOT the cells you want to protect!)
          – Select Format | Cells…
          – Activate the Protection tab.
          – Clear the check box labeled “Locked”.
          – Click OK.
          – Select the cells whose formulas you want to hide.
          – Select Format | Cells…
          – Activate the Protection tab.
          – Tick the check box labeled “Hidden”.
          – If necessary, repeat for other blocks of cells.
          – Select Tools | Protection | Protect worksheet…
          – Tick the check boxes for the features that should be available to the user.
          – Specify a password if you like (you can leave it blank if you don’t want a password)
          – Click OK.

          You can get information about functions such as VLOOKUP in the online help. In short:

          =VLOOKUP(F2,Sheet2!$A:$B,2,FALSE)

          This formula takes the value in cell F2 on the current sheet, and looks up this value in the first column of the range A:B (columns A and on Sheet2. If it finds the value, it then takes the value from the same row in the second column (since the 3rd argument is 2) and returns that. the last argument FALSE specifies that you’re looking for an exact match, not an approximation.

          HLOOKUP is similar, but it looks for a value in the first row of a range, instead of in the first column, and returns a value from the same column but from the row specified in the 3rd argument.

          • #958336

            Hans,

            The VLOOKUP is good for a single sheet. What if I have about 10 sheets and each one I import it from a text file, thus the worksheet keep moving up, sheet 4, 5, 6….. where do I place the sheet 2, in this case, where the VLOOKUP formula was? how can I have the VLOOKUP perform its function on the 10 worksheets at the same time after a host other runs.

            thank you for your advise.

            cheers, kun

            • #958341

              VLOOKUP can only look up something in a single sheet. I don’t understand what you mean by “how can I have the VLOOKUP perform its function on the 10 worksheets at the same time”. Can you try to explain more clearly?

            • #958470

              Hans,

              I have a workbook contain about 10 worksheets which I will need to insert the Abbr in all the cells column A, the abbr will reference to column D which shows the country. eg in column D , its show France, therefore in column A, I will need to insert the abbr. FR.

              I need to insert for all 10 worksheets and is trying to look for an automate solution which allows me to insert the abbr. for all at one go. As I notice that the VLOOKUP which you have provide is good for a single sheet and therefore, every time I will need to change the formula to account for the sheet where the base data is.

              pls advise if you have any suggestion.

              Thanks Always, kun

            • #958492

              The following code assumes that you have named the worksheet with the lookup table Countries. The macro loops through all worksheets and checks if cell A2 is blank. If so, it will fill column A with abbreviations for the countries in column D.

              Sub FillColumnA()
              Dim wsh As Worksheet
              Dim lngRow As Long
              Dim lngMaxRow As Long

              ‘ Loop through all worksheets
              For Each wsh In ActiveWorkbook.Worksheets
              ‘ Only act if A2 is blank
              If wsh.Range(“A2”) = “” Then
              ‘ Last row in column D
              lngMaxRow = wsh.Range(“D65536”).End(xlUp).Row
              ‘ Loop through rows
              For lngRow = 2 To lngMaxRow
              ‘ Set cell in column A to abbreviation for country in D
              wsh.Range(“A” & lngRow) = Application.VLookup( _
              wsh.Range(“D” & lngRow), _
              Worksheets(“Countries”).Range(“A1”).CurrentRegion, _
              2, False)
              Next lngRow
              End If
              Next wsh

              Set wsh = Nothing
              End Sub

              I have attached a workbook with the code.

            • #959285

              Hans,

              Refering to your codes posted higher up in this treads, I am unable to made use of the 1st set, ie, after pasting the code in the module, it does not appear in the marco box when I want to run it. (Maybe I have done it wrongly). For the 2nd set of code, it is only good when the column headings are in the first row, it won’t work if the headings start from the 3rd row onward. Is there a way to have code write for headings which may start from 2nd row onward to not more than 8th row? For example, if I want to delete column with headings; Local Area Value, Domestic Mkt. Price, Book Val, ….see attached sample.
              Pls advise how I can made use of these codes and How can I apply these codes for a workbook contains 15 worksheets at a click of the run button.

              Sub DeleteColumn(ColumnHeading As String)
              Dim rng As Range
              Set rng = Range(“1:1”).Find(What:=ColumnHeading, LookIn:=xlValues, LookAt:=xlWhole)
              If Not rng Is Nothing Then
              rng.EntireColumn.Delete
              End If
              End Sub

              To delete the column with heading “Address”, regardless of whether this is column B or column J or column DE, use DeleteColumn “Address”.

              The following macro will delete the columns with heading “Address” and “Delivery Date”.

              Sub DeleteSomeColumns()
              DeleteColumn “Address”
              DeleteColumn “Delivery Date”
              End Sub

              Thanks Always, kun

            • #959305

              The procedure DeleteColumn has an argument ColumnHeading, therefore it is not a macro. Only procedures (subs) without arguments count as macros. The purpose of DeleteColumn is to be called from DeleteSomeColumns, not to be run by itself.

              To make DeleteColumn search rows 2 to 8, and work across all worksheets, change it as follows:

              Sub DeleteColumn(ColumnHeading As String)
              Dim wsh As Worksheet
              Dim rng As Range
              For Each wsh In ActiveWorkbook.Worksheets
              Set rng = wsh.Range(“2:8”).Find(What:=ColumnHeading, LookIn:=xlValues, LookAt:=xlWhole)
              If Not rng Is Nothing Then
              rng.EntireColumn.Delete
              End If
              Next wsh
              End Sub

              Modify DeleteSomeColumns to delete the column headings you want. You can add as many lines as you need, one for each column heading you want to delete.

            • #958365

              Is the solution to post 494234 what you are looking for? This had a lookup from multiple sheets…

              Steve

        • #957716

          Here is a primer on Excel — Worksheet Functions — VLookup by MS MVP Debra Dagliesh.

          HLOOKUP is nearly identical but instead of looking through a column to find a row that matches, it looks thru a row until it finds a column to match.

          Steve

          • #957740

            Hi Steve,

            Thank you for the explanation and the info, I would definitely look at it and try learn something from there.

            Appreciate your advice.
            kun

    • #957592

      To delete a column with a specific heading, you can use this procedure:

      Sub DeleteColumn(ColumnHeading As String)
      Dim rng As Range
      Set rng = Range(“1:1”).Find(What:=ColumnHeading, LookIn:=xlValues, LookAt:=xlWhole)
      If Not rng Is Nothing Then
      rng.EntireColumn.Delete
      End If
      End Sub

      To delete the column with heading “Address”, regardless of whether this is column B or column J or column DE, use DeleteColumn “Address”.

      The following macro will delete the columns with heading “Address” and “Delivery Date”.

      Sub DeleteSomeColumns()
      DeleteColumn “Address”
      DeleteColumn “Delivery Date”
      End Sub

      Since this is standard code, it should work on any version of Excel from 97 to 2003, without modification. You have to be careful not to use new language elements introduced in Excel 2002 (XP); you can find which they are in the What’s New section of the online help for Excel VBA.

      • #957718

        Hans, it’s fantastic !!

        I never know code is just a two lines ……I thought it is suppose to be long….

        Is there anyway to find a specific ‘text’ in a worksheet and insert 1 at its column E?

        Thanks, kun

        • #957722

          Where do you want to look for the text? In any column?

          • #957736

            Hi Hans,

            It normally would appear in column D or C, the text maybe term or terms and upon finding such word, I would like to insert in it’s column which is column F a number 1, so that these entries won’t be delete off.

            thank you for your advise,
            kun

            • #957856

              Try this macro:

              Sub MarkRows()
              Dim strWord As String
              Dim rng As Range
              Dim strAddress As String
              strWord = InputBox(“Enter the term to look for”)
              If strWord = “” Then
              Beep
              Exit Sub
              End If
              With Range(“C:D”)
              Set rng = .Find(What:=strWord, LookIn:=xlValues, _
              LookAt:=xlPart, MatchCase:=False)
              If Not rng Is Nothing Then
              strAddress = rng.Address
              Do
              Range(“F” & rng.Row) = 1
              Set rng = .FindNext(After:=rng)
              Loop While Not rng Is Nothing And Not rng.Address = strAddress
              End If
              End With
              Set rng = Nothing
              End Sub

            • #957956

              Hans,

              The code given don’t seem to work, it just pop up a dialog box. I copied and paste it directly into the module. I also try paste into the commandbuttion1 of UserForm1, ie right click the commandbuttion1 and select view code, I then copied and paste directly into the code window. It showed Compile error : Expected End Sub and the Private Sub CommandButton1_Click() was highlighted.

              Private Sub CommandButton1_Click()
              Sub MarkRows()
              Dim strWord As StringSub MarkRows()
              Dim strWord As String

              Pls advise.

              thanks, kun

            • #957964

              You cannot have two lines with Sub … following each other. Each Sub … must be followed by lines of code, then End Sub before the next Sub …

              You can call MarkRows from CommandButton1_Click:

              Private Sub CommandButton1_Click()
              Call MarkRows
              End Sub

              The code itself works. I have attached an example (it uses a command button from the Forms toolbar, it runs the MarkRows macro directly. You can right-click the button to view its properties and to see which macro is assigned to it.

    Viewing 1 reply thread
    Reply To: Auto Insert and Column delete (Office XP )

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: