Auto Insert and Column delete (Office XP )

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote