I'm making excel macros for the first time and getting things figured
out, but I have a few questions:
What code do I use to delete columns that have all empty cells EXCEPT
the top cell (row 1) that contains any of a number of headings? I need
to insert this into an existing macro.
What code do I use to fill color every other row (preferably even numbered)
light grey after selecting a "currentregion" ? Again, to put inside an
existing macro.
A related question: When I find code help on the web, it usually
starts with "sub" and ends with "end sub" so how do I insert that into
an existing macro? It looks like a "sub within a sub" doesn't work.
In posting macros I would like clor some lines of the macro. I tried writing the macro in microsoft Word and colored the lines and copying to the board, but the corlor does not come up.
I have inherited a few workbooks for some monthly reports, and there appear to be several macros in the book that are not used anymore. I noticed them when I tried to run one of my own macros using Tools-->Macros--> Run Macros. While they are listed in the run macros list, I can not select "Delete" and there are no modules in the VBA editor besides those I inserted myself. If I try to run one of them I get the error "Reference is not valid."
I have used the following Macro that deletes the Macro code. It then saves the file and quits excel.
************************************* Sub removeAllCode() Dim awi Dim awcl As Integer Dim count As Integer Dim i As Integer 'loop counter On Error Resume Next count = ActiveWorkbook.VBProject.VBComponents.count For i = 1 To count Set awi = ActiveWorkbook.VBProject.VBComponents.Item(i) awcl = awi.CodeModule.CountOfLines awi.CodeModule.DeleteLines 1, awcl Next i Set awi = Nothing ThisWorkbook.Save Application.Quit End Sub *************************************
However when I open the Excel File, I still get the Macro Prompt. I have checked in Macro list as well as in VB Editor and no Macro code exists
If I have the file manually (or do a save as) and then reopen the file, I no longer get the Macro prompt.
I have tried various examples listed in different forums/sites but nothing works.
I have a macro I found here on the boards written by Lenze to delete an entire row based on what is found in column A. I would like to delete any row where Col. B contains 10 or less characters and I have modified it to do so (or at least I think it does). My problem is that it takes about 12 minutes to run the macro (I have about 50k lines to run through). I was wondering if this is the fastest method or if it examines things other than just column B.
Sub Test() Dim i As Long LR = Cells(Rows.Count, "B").End(xlUp).Row For i = LR To 2 Step -1 If Len(Cells(i, "B")) < 11 Then Cells(i, "B").EntireRow.Delete Next i End Sub After this runs, I am left with Columns A to somewhere around AH. The columns are generally in the format of text followed by a numeric column. An individual text column has the same name through all of the rows. The numeric columns have varied values whether negative or positive. Ideally what I would like: If a given cell (ie. C2) in Row 2 is numeric, then copy the cell to the left (ie. B2) into (ie. C1) and then delete Column B. I need this to work for multiple columns from B to C, skip D and E, and then from F to AG (and maybe beyond).
In column A, there are 13000 plus entries mixed with General and Text Formats.:
12234
1223
01234
the cell containing number starting with Zero is formatted as "text". The problem is that there are certain spaces given after some entries in certain cells. How those can be located and deleted.
In my column A I have a space before all my text in every row......how can I delete that? I have a lot of records so doing it for each will take forever.
Need the code for deleting multiple columns simultaneously in an excel depending on some search criteria.Suppose I want to delete the columns whose names are "Roll No" ,"Age" & "Class"
I have a worksheet that I use to store several static lists which populate various combo boxes in my application. However, I'd also like to use this worksheet to store a single column of data which can shrink or grow dynamically.
I have no problem with finding the last cell and adding data to that cell, but I'm having trouble with how to go about deleting the value of a single cell.
So as an example, If I've populated Cells A1:A10, and I want to delete the value that's in A6, how do I go about moving cells A7:A10 up one to fill in any empty spaces?
Does anyone have a utility which can be used to select a specific column i.e. B:B, which then 'selects' all duplicate values in that column prior to deleting them ?
Working on some code that will find the Column Name "CL" and delete the entire column. Here is what I have so far...it does not give me a error when it compiles, but for some reason the statement in red is being ignored. I tried switching Range with Columns, but that did not work.
Private sub coldelete () Dim Rng2 as Range
shUCLR = sheets("Unit Cost").usedrange.rows.count Set Rng2 = Sheets("Unit Cost").Range("A1:BF1")
For Each d In Rng2 If d = "CL" Then colnum = d.Column colletter = Left(Sheets("Unit Cost").Cells(1, colnum).Address(False, False), (colnum)
I had this question earlier and I dont know if it just isnt a regularly answered section so I though i would ask it here.....better yet I will just link to the other page so you can see whats up. Hopefully i will get a few more hits on it in this thread.
Is it possible to set a formula or something up so that if a row contains certain information, then the row will turn a certain color? For instance, if cell B2 contain the number "3" and cell B5 contains the number "5", then row B will turn yellow. Then if cell C2 contains the number "4" and cell C5 contains the number "7", row C will turn blue. This is just an example. In this example, I'd like a formula all the way down column's 2 and 5, but only the rows would change color (not columns) according to their information.
I have got a Pivot table with makes and their sales in 2008 and 2009 like this 2008 2009 BMW 100 90 Audi 80 80 Mercedes 200 150 Fiat 80 120 Honda 100 110 Peugeot 70 120 (unfortunatley the formatting is deleted when posting).
I have created a stack column chart. In that chart I want certain makes to have certain colors like BMW = blue, Audi = yellow, Mercedes = black, Fiat = green, Honda = red and Peugeot = purple When I update the Pivot with a new month and a make changes position the colors go wrong. I would like to create a script that fixes the makers to their colors, so that after an update I can just run the script and am done .
Unfortunately I do not really know how to work with VBA (I can just steal macros and modify them a bit to my needs or use the recorder).
In an excel sheet, I have 5 columns namely, name, address, source, subtype,code. In subtype column we enter the the type for example: name,SBI bank then subtype will be bank, and if it is SBI atm then subtype will be ATM etc. Similarly all the subtype will be given a code like 4 for ATM and 10 for Bank respectively. My query is that , is it possible to update the CODE column by comparing the subtype column using macros in excel.
I have many excel wordlist, which are being converted from other file formats. Now there are sometimes useless spaces before entries in the cells. There are also some times commas and other not needed signs at the beginning of a cell in a column.
I need a macro, which removes spaces, symbols, letters, numbers or combination of them from the beginning of all cells in a column. The symbols should be chosen before the macro starts its work.
For example:
Before running the first macro:
Column A ,Word1 ,,Word2 Word3 ,Word4
After running the macro the beginning should be cleaned from chosen symbols, spaces or signes. (in our example if a cell begins with , then the commas should be deleted).
In Column "C" there is a list of numbers ( these number realte to incidentsm and are called incident IDs) and I want to delete the rows that have duplicate incident IDs.
How can I delete rows in a worksheet using values in a certain column as criteria? Example: I want to delete all rows that show a value of zero in column k. How is this done?
I am trying to create a search function in a database I have made. I have a userform with drop down boxes that people can choose what they want to search for. Once they have chosen and clicked the search button I want it to search only in the relevant column, i.e. search for the word in the dropdown for "engine" only in the "engine" column of the database. Once it has found the appropriate cells with the answer, I want it to delete all other rows that don't include the answer.
Once it's done this it needs to do it again for the other fields, unless they are empty. I have used 'If engine_type <> "" Then' as the starting point so that it only searches chosen fields.
I need to coloring percentage values in excel 2000. What I would like to do is have percentages greater than zero shown in green, while negative values are red, and zero values are black. I currently have the custom formatting so negative values are red and all others are black, but I cant' seem to find a way to change it so greater than zero numbers are green and zeros are black. All I can do is chang positive and zero at the same time. Here is what I currently have:
Cells in column B need to be colored red if they are equal to 50% of the cells in column C in the same row, or if the cell in column C in the same row is 100.
With a range of cells (a1:ao41) I want to color several cells depending on the value of that particular cell and another color when that particular and a corresponding cell have both the same value. It's some sort of sociogram The first row and column both contains names of students. Let say the students have to choose the three most populair ones to work with and the three most unpopulair ones. Positive ones get the color blue, negative ones get the color yellow. That's easy to do with conditional formatting. But now comes the tricky part. If for instance Student 3 chooses Student 7 and Student 7 chooses Student 3 the color of both cells have to be green if both positive and red if both negative. That's also do-able with conditional formatting, but it most be much easier to do with VBA. I just don't know how .
I have reviewed many a thread today looking up how to color in cells c11 through c20 without conditional formating.
if these cells have a time less than 01:00 then they turn green if these cells have a time greater than 01:00 then they turn red if the time is equal to 01:00 then they turn white.
