So I want to add some VBA Code that deletes specified columns and then insert Bolded Column headers to the remaining columns. The columns I want deleted are D,F,I,J,K. For simplicity purposes, the column headers would be A,B,C,D,E,F,G.
I tried using Columns("D,F,I,J,K").delete but I kept on getting 13 error.
[Code] ......
Attached File : Test VBA File.xlsx
I am paste valuing from one sheet to another. The size of the data changes each time so to manually delete all Null cells is a pain.
If I do not delete them it sucks up 3 megs of space. I have searched the board several times looking for a solution, lots of discussions but no solutions.
It would be great if I could find that first cell that contains the Null string and then delete the cells remaining in the column. Any suggestions would be greatly appreciated.
I have a spreadsheet with several columns (all column names are in row 1).
I have 2 of column names. Note that their actual column# might change so the macro would have to look for the column name and NOT the column position ...
I need a macro to find the last cell in the column, then copy the formula to the next blank cell. Then, it goes back to the last cell (above) and paste's values. Then, go to the next column and repeat the process. I can do this but have to call each cell separatly...however, I would like to do it in a loop to simplify things. It would be great to even be able to just set the start and ending columns. Here is my current code:
Dim rng As Range, aCell As Range
Set rng = Range("C8, D8, E8, F8, G8, H8, J8, K8, L8, M8, N8, O8, P8, Q8, R8, S8, T8, U8")
For Each aCell In rng
Selection.End(xlDown).Select
Application.CutCopyMode = False
[Code] .......
It does not go to the next column, instead it stays in the same column and repeats the process.
I'm trying to use VBA code to delete certain rows based on a couple of criteria as follows:
1) If column header is "TERMINAL NAME" and the cell value in that column is "BONDDESK", I need the entire row deleted.
2) Similiarly, if the column header is "PRODUCT TYPE" and the cell value in that column is "CORP", that entire row needs deleted as well.
The information is coming from another source, so the columns may change from day to day, i.e, "TERMINAL NAME" may be in Column L one day but in Column O another day.....
Can I create a PivotTable with two columns of data for the same Column header?
I have created a PivotTable in Excel 2003 with months for rows and cities for columns. I would like to have TWO columns of data for each city. The two data columns are: Average House Selling Price, and Number of Houses Sold. When I put both of these data fields into the PivotTable Wizard, they are listed below each other so that each Month occupies two rows, but each city occupies one column. I want the two data fields beside each other so that each month only occupies one row, but there are two data columns for each City.
I have VBA code to perform some actions on data in excel file and then convert all that data into semi-colon separated CSV/text file (code below).
Now, all I want is to add VBA code in the existing macro to find a column header (say, "Application date") and then convert all the dates into YYYY-MM-DD format. The original values in this column don't have a fixed date format.
Code:
Public Sub ExportToCsvFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendDataOnExistingFile As Boolean)
Dim WholeLine As String Dim FNum As Integer
Dim RowNdx As Long Dim ColNdx As Integer
[code]....
I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.
i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2
(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4
test.bmp
I am a macro newbie and I think this is beyond me.
I've been trying all morning with no success to make a macro that will copy data from all columns of one workbook containing specified header text to a single column in a different workbook.
So for example, I've got a workbook called coupon barcodes that has multiple tabs for each person redeeming coupons on sheets 2-88 (sheet one can be skipped) and some of the columns are labeled "voucher 1" in cell A4 or B4 or C4 etc. with a list of all the coupon barcodes that customer redeemed below that. I want all of the data from all of the columns in this workbook that have the header "voucher 1" to be copied and pasted into one column (order doesn't matter) also labeled "voucher 1" in another workbook I have open called vouchering database.
There must be a way to do this that is easier than searching the internet all afternoon again
I am looking for a method which would delete empty cells in a row, and then shift the remaining data to the left (filling the cells with data). I know that this could be done manually, but I need it completed for 3000 rows! I have attached a simple sheet to show what I'm Looking for.
View 2 Replies View RelatedIn the code below I find rows containing "$$ #" at the far left and process it.
For all the remaining rows I want to delete the entire row, my macro only deletes some rows. Obviously I am not grasping something about looping throught the rows to delete unwanted data.
I would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.
And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth
I have an excel spreadsheet that has several fields which should be connected. If one of the fields is selected via a drop down, then several other columns should autopopulate with the data in the list set up on another page. What Im trying to figure out is:
1) How do I make it so it does this with every row on the spreadsheet? I've seen code in the formula bar so that it works for one row, but I need it to work regardless of how many rows are added to the sheet...
and
2) Is there VBA code so that if someone selects A value from a drop down list in, say, column F, that data corresponding to that selection is placed in other columns in the sheet?
How can I program a macro to delete columns based on what column header contains? For example, I want to delete entire columns (including header) if the corresponding cell in the header row contains the % symbol.
View 6 Replies View RelatedI've been trying find an appropriate formula to extract the column header from a table in a different sheet if the row header and value in that table is known.
in the lookup table the row titles (column A) are product codes, column titles (row 2, D through AX) are business names and the table values are quantity.
In a different table I have product codes in column A and in column B i have the max number/quantity of products for that code. In column C i want to put the company name associated with the product and the number/quantity.
A column (A1:A60) is being progressively filled in with numbers, and occasionally some blank cells are between.
I need a formula to work out the number of the remaining cells (that are yet to be filled in) from the last entry till the last cell (A60) of the column.
I'm looking for the correct way of deleting columns based on if row 2 has an x in it..
I have two versions that I tried but I am pretty sure there are faster ways of doing it, I don't quite know how to delete all the columns at once.
[Code] ......
The first version doesn't work for some reason and the second column works but is a slow loop, what to do to make this faster?
I have column A that is 1,500 records.
I have column B with 40,000 records.
Among the 40,000 records in column B, duplicates of ALL 1,500 records from column A exist.
My question is: How do I find WHERE the 1,500 dupes are and how can I delete JUST those records?
I have a subroutine to delete non-header rows. This sub routine seems to choke on the delete line.
Code:
Sub CleanSheets(Header As Integer, WorkSheetToClean As Worksheet)
Dim WorkSheetRows As Integer
WorkSheetRows = WorkSheetToClean.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
WorkSheetToClean.Rows(Header, WorkSheetRows).EntireRow.Delete
End Sub
I am looking for a macro to look in Sheet 1 column A and compare the values to Sheet 2 column O. When it finds a duplicate I want it to delete the entire row in sheet 1. I dont want to have to manually sort anything if that's possible.
View 3 Replies View RelatedFor column "B" count all the 1's if column header is equal to name in column A.
For Column "C" needs to be checked if a value was filled in column "I" if yes then check if in column "L" has a value, count all these values.
I am looking for a better solution working with columns in VBA.
Currently, I refer to columns by their letter or # in my VBA code.
For example:
Columns("AW:AW").FormulaR1C1 = "=+RC[-8]"
I have a scenario where I have to work with a dynamic data source that scrambles columns around (column is not always in the same position on the spreadsheet). Currenct...Also, there may be missing columns or new columns that need to be recognized.
What I would like to do is refer to my columns by header name.
For example (PSUEDO CODE):
If column ("MTD BUD") or ("MTD REV") do not exist Then
Goto ErrorHandler
Else
Columns("MTD REV").FormulaR1C1 = "=+RC[MTD BUD]"
End if
Would the best way to go about something like this be to create an array with column header names that I can run things against? How would this look?
(column headers are always in row 2)
I am trying to write a formula where the column header of the row in which a value other than 0 exists, will display for each instance (row) where a value exists in an array spanning 3 columns. So the result cell could be any of the three column headers, or a combination thereof.
I started the formula in P2 of the GL Detail-2012 tab. File attached.
Here is what I started: =INDEX($M$1:$O$1,SUMPRODUCT(COUNTIF($M$2:$O$67756,))). Not working.
I would like to display the column header of the row in which a value other than 0 exists in a cell for each instance that value exists in an array spanning 3 columns. So the result cell could be any of the three column headers.
I started the formula in P2 of the GL Detail-2012 tab. (FILE TOO BIG)
Excerpt:
Resin Sand Eliminated Segment
- (12,896.65)-
- (6,570.85) -
- (11,503.80) -
- (13,188.50) -
If there is any value other than 0 in Resin, Sand or Eliminated columns, return that column's header under Segment.
Here is what I started: =INDEX($M$1:$O$1,SUMPRODUCT(COUNTIF($M$2:$O$67756,)))
I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).
Here's data table:
CriteriaHeader 1Header 2Header 3Header 4
ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00
CriteriaNumberValue
DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))
[Code] ......
This works, UNLESS "My Column Header" is the last column, or second to last column, then it jumps left two columns, instead of landing on the correct column.
The purpose of this script is to select a cell directly in that column that I was searching for.
I need a formula to return column header if there is data in the column, any data.But I also need to return the second, third, fourth header with data too
In column A row 2, I have =IF(M8"",$M$1,IF(N8"",$N$1,IF(O8"",$O$1,IF(P8"",$P$1,IF(Q8"",$Q$1,IF(R8"",$R$1,IF(S8"",$S$1,IF(T8"",$T$1,""))))))))
This returns the row 1 header for the first instance of data in row 2 columns M thru T But in B2, I need the second header with data in the column.
I need the results in yellow (1st, 2nd, 3rd, 4th) from the array to it's left
1St match2nd 3rd 4thColumn MColumn NColumn OColumn PColumn QColumn R
Column MColumn PColumn QData data2Data3
Column NColumn OColumn PColumn R Data Data2 data3 data4
Would use match/index but can't increment it to the second or 3rd match
I want to export some columns of my worksheet to a csv-file. But i don't want to export all of them, i just need for example: Columns B, D and G and inside the csv-file they should be in an different order like G, B, D .
I already thought about using "union" to select the specific columns, but i can't get this to work.
I have the following code that sorts each column sperately and it works, but believe this is a long way round and not the best way. I need the columns sorting alphabetically seperately and each has a header row.
View 2 Replies View RelatedIn row 1 I want to have the names of servers, so we would have A C D E
Under each of those I want to have 4 other columns, so A would have on row 2 Start, End, Data, Time, or something like that.
Then B would have under it Start, End, Data, Time
And so on for C D ...
I would then want to sort it by the top level row, so if I had to insert B at the end I could sort it so it would be
A B C D E with all of the Start End Data and Time for the server to be moved along with it's master header.
I tried setting this up but then I went to sort it told me it could only sort if the columns were the same size, so having a merged top level A with four things under it did not work.