Macro For Entire Column
Nov 12, 2013
I have created one excel which contains column with range. (A1:A6). and the drop box included the values as Passed, failed, NA, NC) i have coded the the macro to change color as if i select passed from A1 then A1: F1 will be Red. please find the below code.
Sub Prasanna1()
Range("A1").Select
Select Case Range("A1").Value
Case "Prasanna"
Range("A1:F1").Interior.ColorIndex = 7
Case "Kale"
Range("A1:F1").Interior.ColorIndex = 0
[Code]...
The code is working fine for one row. Now i want to set the same code for entire column A. as if i will select A10 the same range of row should get change with fill color.
View 3 Replies
ADVERTISEMENT
Jun 2, 2014
I recored a macro to Cut the entire "E" column and paste on "A". For some reason when i read the code it doesnt capture the part where it needs to paste it in column "A".
View 1 Replies
View Related
Aug 14, 2007
I have Excel 2002. I need to create a formula that will move an entire row to another page within the same workbook if a cell in that row has a certain value. Example would be if A1 cell in the row has a value of 111 I need to move the entire row to another page. I could use the sort then cut function in a macro but the spread sheet I'm working with changes daily. One day there will be 10 A1 cells with 111 the next 30 cells with 111 in A1 and some days no cells with 111.
View 4 Replies
View Related
Mar 29, 2013
I've seen a few examples of macros to delete the row if the first letter is something, but not if the first 2 is equal to something.
I'm looking to go thru every row that contains data and look in Column B to see if it starts with P4 or P5. If that's true, then delete the entire row.
View 8 Replies
View Related
May 17, 2013
with performing the following tasks with VBA:
1). For each cell that in the range that has a Red background delete the entire column
I have attached a sample spreadsheet where I have tried to do this. It's not quite working. It does not seem to work when there are RED cells next to each other.
I know that I can do a simple thing like column("A:A"), but I'd rather have it in a loop as in the sample code, in case the columns change in the future.
2). The second task is to delete the rows in the spreadsheet where the first cell in the row is a blank.
View 4 Replies
View Related
May 7, 2014
I need an easy code that searches all of column A and deletes the entire row if the cell has the value "-". It needs to find the last row of data using something like LastRow = Range("A" & Rows.Count).End(xlUp).Row
View 4 Replies
View Related
Nov 2, 2008
I am trying to write a macro to do the following:
Search column "I" for blank cells. If one is found then all adjacent cells to the left in that row are shifted to the right by one. (in other words for that row cells A-H are shifted right to become cells B-I).
View 3 Replies
View Related
Nov 2, 2008
Search column "I" for blank cells. If one is found then all adjacent cells to the left in that row are shifted to the right by one. (in other words for that row cells A-H are shifted right to become cells B-I).
View 2 Replies
View Related
Feb 20, 2009
Is there a macro to delete an entire row if a duplicate entry appears only in a certain column.
1. Look for the column header with the name "File Number"
2. Anytime the same number under the "File Number" column appears more than once in that column, keep the row that contains first occurrence of that number buy delete the entire row anytime that number is repeated in another row in that same column.
This is regardless of what is contained in the other columns. For example..let's say these cells contained this data...
B1 - UTE00225
B2 - UTE00546
B3 - UTE65513
B4 - UTE00225
B5 - UTE00225
In this case, I would want to keep rows 1, 2, and 3. But, I would want to delete rows 4 & 5 because the number "UTE00225" has already appeared first in B1. I'm using Excel 2003.
View 2 Replies
View Related
May 19, 2014
I have an Excel database of department contracts with a Master worksheet. I have created a worksheet tab for each of the departments. Column G has a dropdown list for each department. When I enter a new contract onto the Master I want it to auto copy to the coordinating worksheet based on the selected department.
If possible I would also like it to enter the newest contract would enter into the coordinating Contract Party entered into column D. (ie... If Hospital A is entered in column D on master, the new entry on the worksheet being copied to will be entered under the last Hospital A, if Hospital B is entered on Master it copies under Hospital B). If this isn't possible then I am not too worried about that part.
I have attached a blank version of the file. Current Master 5-15-14.xlsx‎
View 3 Replies
View Related
Aug 18, 2009
Does anyone have a macro that will delete entire row if column a matches column d
View 6 Replies
View Related
Feb 10, 2012
I need to do the following:
I have a list of cities on one sheet. The headers are the counties and below each (6 columns) are all the cities names.
I need to be able to show all the cities of a specific column on a nother sheet (autofilling column down with all cities names) when i type the name of the related county in an adjacent cell.
View 2 Replies
View Related
Jul 17, 2013
The subject summarizes what I want to achieve. I just want to tie an if statement to an entire column, so it will disappear if a statement is satisfied.
View 4 Replies
View Related
Apr 5, 2013
I'm inserting new information at the top of my worksheet. Unfortunately the formula for what WAS row one: =SUMIF(TotalSales!$B:$B,A1,TotalSales!$H:$H) Will no longer work for row one because as more date is collected row 1 shifts down to row 2. Row two still looks good but there is no longer a formula for the new rows inserted.
To be clearer... I would like to have the following:
=SUMIF(TotalSales!$B:$B,A1,TotalSales!$H:$H) in cell B1
=SUMIF(TotalSales!$B:$B,A2,TotalSales!$H:$H) in cell B2
=SUMIF(TotalSales!$B:$B,A3,TotalSales!$H:$H) in cell B3
How can I keep that formula in row one as I keep inserting new rows?
View 2 Replies
View Related
Apr 9, 2009
I have a work book with a lot of information. In column “W” I have a text comments, cells in this column probably have something or not, and the text is very variable. What I need is an instruction to look at entire column for two key words for example: "na co", "apna" or "apcredit" ... in case of one of these words its found I need the macro to make a process.
For example if in the column W there is any item (cells) with text "apnapo" then
First filter the information:
Selection.AutoFilter Field:=23, Criteria1:="=*apna*", Operator:=xlAnd
Range("K1").Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.EntireRow.Hidden = False
ActiveCell.Offset(1, 0).Select
Loop
Then write down something in columns K & L
ActiveCell.FormulaR1C1 = "Ion Apnapo"
ActiveCell.Offset(0, 1).FormulaR1C1 = "Pamela Elizondo"
Selection.Offset(0, -1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, 1).Select
Selection.FillDown..................................
View 9 Replies
View Related
Feb 19, 2010
As the title sugests I would like to Select a cell and a macro will highlight the row and column
Sub Select_Entire_Row()
Dim RowNo As Integer
Dim ColNo As Integer
RowNo = Selection.Row
ColNo = Selection.Column
If RowNo.Value >= 1 Then
Cells(RowNo, ColNo).EntireRow.Select ' I want it to do both not one then the other
Cells(RowNo, ColNo).EntireColumn.Select
End If
End Sub
View 9 Replies
View Related
May 2, 2006
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"
View 2 Replies
View Related
Oct 1, 2012
I have two worksheet. One worksheet (ws1) contains a list of item I want. The other sheet (ws2) contains multiple columns where the header (row 6) is named by item name.
I have the following code which deletes the entire column if the header name is not in the list contained in ws1 :
VB:
Sub delete_col()
Dim wanted As Boolean
Set ws1 = Workbooks("test1").Sheets("aaa")
[Code]....
First of all, this loop does not work properly since deleting the entire column shift them on the left, so when I first analyze column 11, if I delete it and then analyze column 12, the real column 12 now became column 11 and so on...
Secondly, this code is pretty slow. I am pretty sure I don't have to loop through my initial item list everytime I do Instr on a new column.
View 9 Replies
View Related
Jun 26, 2009
i know how to make a drop down list and then I can strech it over X rows but how do I set somthing like from C3 and forever onwards (C4,C5,C6..)use this droplist. I do not know how many entrys will be made so from C3 and onwards I need all cells to have this drop down menu.
View 2 Replies
View Related
Jan 16, 2010
I have a list of number in a column that there is over 22,000 is there a away I can add ".jpg" ad the end of each number?
View 10 Replies
View Related
Dec 17, 2012
I want A1:A400 to display in B1.
View 6 Replies
View Related
Feb 21, 2013
I have columns
Row 1 is heading..
IF Column C doesn't have data in entire column then delete C D E F
IF Column D doesn't have data in entire column then delete D E F
IF Column E doesn't have data in entire column then delete E F
IF Column F doesn't have data in entire column then delete F
Same way for heading NN's
IF Column G doesn't have data in entire column then delete G H I J K L M N
IF Column H doesn't have data in entire column then delete H I J K L M N
IF Column I doesn't have data in entire column then delete I J K L M N
IF Column J doesn't have data in entire column then delete J K L M N
IF Column K doesn't have data in entire column then delete K L M N
IF Column L doesn't have data in entire column then delete L M N
IF Column M doesn't have data in entire column then delete M N
IF Column N doesn't have data in entire column then delete N
View 1 Replies
View Related
Apr 15, 2014
i want to delete entire row if D2:D10000 has a numeric value e.g .111 to 100000.1114
View 4 Replies
View Related
Apr 16, 2014
i want to delete entire row if Column C2:C1000 are blank
e.g if range c2:c100 have data then delete the rows C101:C1000
View 3 Replies
View Related
May 5, 2014
I have fixed headers on row 16, from columns A-AC.
I want to be able to delete the entire column, if the row has a certain string, such as "Chart ID" .
I also want to expand it to include other strings such as "Month" and "Source" . So if it contains any of these words, the columns should be deleted. It should be an exact match (as other headers contain the word "month").
View 12 Replies
View Related
Mar 3, 2014
Im trying to compare sheet 1 Column A, with sheet 2 Column A, and then move any duplicates (along with the row of data) from sheet 2 over to sheet 3.
For instance, Employee Numbers that match in both sheet 1 and 2, remove that entire row from sheet 2 to and insert it into sheet 3.
VBA that compares sheets and moves duplicates found in column A to sheet 3 but it leaves the rest of the row on sheet 2.
[Code] ......
View 10 Replies
View Related
Oct 13, 2009
I have a spreadsheet that I use to populate a MySql database. It has a hierarchical relationship in the spreadsheet which is accomplished through a column called GUID and another column called Parent GUID. I've been using a website to generate GUIDs for me and pasting them into the first column one at a time, then copying to the second column where necessary. It takes forever.
So, I'm wondering if I can write (or better - paste!) a function that will automatically generate the first column of GUIDs. I've googled and found several functions that create a guid, but I don't really understand how to call the function from excel. Also, I don't want to manually call it thousands of times. I'd just like to call it once and have it generate GUIDs for the entire column.
I'll still copy and paste to fill in the Parent GUID column.
View 5 Replies
View Related
Apr 11, 2012
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)
View 2 Replies
View Related
Jul 5, 2007
how to limit the data a user can enter into any cell in an entire column to just spaces and letters a-z.
View 9 Replies
View Related
Jul 17, 2007
I want to copy a D1-C1 formula all the way down column E. If I don't have any data in column D1 and C1, however, I don't want anything to appear in Column E.
Is there a way to hide the formula once I have built it for the all of Column E? The reason I need this to happen is because I can't have any zeros popping up in Column E, because I am calculating a running average of Column E and "zero" values would throw off my average.
View 9 Replies
View Related
