Macro To Change Columns To Rows
Mar 4, 2008Excell macro to change columns to rows example
From this:
A B C
1 3 5
2 4 6
to this
A 1
B 3
C 5
A 2
B 4
C 6
ADVERTISEMENT
Macro To Add Rows After Each Change In Values Over Two Columns
Jun 22, 2013I'm trying to create a macro that will add a blank row each time the values changes in two different columns. I’ve got a macro that sorts the data on two columns, just need to add rows after each change in both columns.
I've found something that'll add rows after each change in one column, the problem is adapting to add rows after each new value in a 2nd column as well.
What I have so far works until it hits the first empty row inserted after sorting by first column.
Sub SplitList()
'
' SplitList Macro
' Macro recorded 05/03/2004 by GaryB
rw = 5
myval = ActiveSheet.Cells(rw, 4).Value
[code].....
Here’s a data sample after performing my sorts, but before the macro to add spaces:
Another, Guy CDP EX 8.36 8.36 0 0 0 0 0
Jones, Some CDP SS 3.3 3.3 0 0 0 0 0
Justa, Nother ESP NV 22.45 22.45 0 0 0 0 0
Person, One ESP SS 23.36 23.36 0 0 0 0 0
Tom, Rich SSP MA 2.35 2.35 0 0 0 0 0
Dude, Steve SSP UN 63.86 25.36 5 1 2 1 1
Blanke, Spaece SSR MA 12.24 12.24 0 0 0 0 0
Try, Again SSR MA 15.25 15.25 0 0 0 0 0
And after the attempt to add rows: (note that it added a blank row between the first and 2nd rows (which differed in the 3rd column), but that it then stopped.
Another, Guy CDP EX 8.36 8.36 0 0 0 0 0
Jones, Some CDP SS 3.3 3.3 0 0 0 0 0
Justa, Nother ESP NV 22.45 22.45 0 0 0 0 0
Person, One ESP SS 23.36 23.36 0 0 0 0 0
[code].....
And, what I’d like it to look like:
Another, Guy CDP EX 8.36 8.36 0 0 0 0 0
Jones, Some CDP SS 3.3 3.3 0 0 0 0 0
Justa, Nother ESP NV 22.45 22.45 0 0 0 0 0
Person, One ESP SS 23.36 23.36 0 0 0 0 0
[code].....
You can see, it seems to be hanging up once it hits a blank row after first round of inserted rows.
Change Rows To Columns
Aug 3, 2006This is really hard to explain by words. check the 2 files I attached in this post. I checked VIRUS already. NO VIRUS. I have the table like in FROM.xls and I want to change it to a table like in BECOME.xls -automatically- because I have more than 5000 records in my 'real' file.
View 5 Replies View RelatedChange Linked Cells From Rows To Columns
Sep 9, 2009I'm working on right now is a spreadsheet where we catalog and monitor account information for a handful of groups. I have several different worksheets that contain grouped information such as all groups we have and a list of individual people in those groups. Those groups are in a single column going down and the people are in a single row for that group going from left to right, an example is below:
Master Personnel Listing for Accounts
-------------------------------------------------
Group 1 | Name 1 | Name 2 | Name 3
Group 2 | Name 1 | Name 2
Group 3 | Name 1
Group 4 | Name 1 | Name 2 | Name 3 | Name 4....................
Getting Rows To Columns Macro Skipping Blank Rows
Dec 28, 2011I have this in Column A, with about 120 entries:
Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1
Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2
Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3
I want this:
Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
all the way down.
I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.
Macro To Change Colour Of The Rows
Jan 22, 2009I've got spreadsheet with lots of data in and the most important data is located in column C which determines the colour of that whole row. The numbers in that column are 9 digits (i.e. 180041960). If that number is the same in the next row highlight both rows of filled cells in yellow and continue to highlight in same colour until the number changes. As the next number is different leave colour as it is until the number changes again then go back to yellow and change colour of rows. Continue the procces until end of spreadsheet.
View 9 Replies View RelatedMacro - Copy Multiple Rows Of Data To New Sheet On Change
Dec 23, 2008I have a multiple rows of data, but one column which is K has data like:
1020
1020
1020
1020
1020
1030
1030
1030
1030
1050
1050
1050
What i want my macro to do, is whenever that number in column K changes to copy all the data in row A thru K to a new sheet.
Moving Rows And Columns With A Macro
Jun 5, 2009I have a massive data file with thousands of lines of data. Each line is a pair, for example
View 6 Replies View RelatedVBA Macro Involving Rows And Columns
Mar 2, 2012I have have a problem in my vba code
if my activecell is on example range("W78") then it will go to range("I5")
I made a code but it does not work, i cant use offset cause i have may columns but they all refer to the same column "I"
this is my example: Range("I & activecell.row").value
Macro To Match Rows In Two Columns
Sep 6, 2007need a quick macro to match rows in two columns as per demonstartion below.
View 9 Replies View RelatedMacro To Hide Rows And Columns
Aug 1, 2008The macro code that will populate and input box and ask you which range of columns and range of rows you wish to hide, hide the columns and advise you via a message box that it has been completed
Macro For Rows And Columns Interchanger
Nov 4, 2008I saw a macro written in this forum where we can select 256 rows to interchange them to colums in sheet 2. I need my first 10 rows to be in first column and next 20 rows in second column in sheet 2 ,like that it goes on . I am a very basic user of Excel .Can i get a macro with some condition that helps me in the above case.
Concatenation Macro For Variable Rows And Columns?
Apr 12, 2012Our company gets excel spreedsheets with UPC numbers. The numbers are divided in parts or columns. Sometimes it's in 2 columns; sometimes in 3; and sometimes in 4 like this:
A_____B_____C____D
023__14444__779__9
023__14442__789__7
I'd like to be able to concatenate the digits and have the entire UPC number appear in a blank column to the immediate right and go straight down the line and calculate them all. I figured out how to do this with separate macros for 2, 3, and 4 digits or columns.
Sub Combine_2_Part_UPC()
Do While ActiveCell ""
ActiveCell.Offset(0, 1).FormulaR1C1 = ActiveCell.Offset(0, -1) & ActiveCell.Offset(0, 0)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Is there a way I could do this with just one macro, by maybe selecting the digits of the first UPC number in the first row of the data to cue the macro in as to how many columns to process?
(The UPCs are in the midst of a lol of other data like product cost, so a macro probably wouldn't be able to figure out how many to calculate on its own.)
Macro For Copying Cells To Columns And Rows
Sep 22, 2012Why does my macro mess up on second line C23 and not places Play Equipment or the amount in the right column it stays on row C22
[URL]....
Code:
Private Sub CommandButton1_Click()
Dim SheetName As String
SheetName = "Estimate1"
SheetName = InputBox("enter the name of a sheet to use", "sheet name", SheetName)
[Code]....
Hide Rows In VBA Macro By Checking 2 Columns
Oct 8, 2009I've tried using multiple loops in the forum but cannot seem to figure out how to actually get them to work properly using the conditional VBA codes on two separate worksheets. The first code snippet is checking cell values from row 6 to 148 as such:
Sub Check_Shifts()
'Insure all shift entries are completed
If Range("K6").Value < "1" And Range("I6").Value < "1" And Range("G6").Value < "1" Then
Range("G6").Value = Range("F6").Value
Range("I6").Value = Range("F6").Value
Range("K6").Value = Range("F6").Value
ElseIf Range("K6").Value < "1" And Range("I6").Value < "1" Then
Range("I6").Value = Range("G6").Value
Range("K6").Value = Range("G6").Value
ElseIf Range("K6").Value < "1" Then
Range("K6").Value = Range("I6").Value
End If
If Range("K7").Value < "1" And Range("I7").Value < "1" And Range("G7").Value < "1" Then........................
Macro To Fill Cells Of Rows Based On Two Other Columns
May 28, 2014Data file with few columns. There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
See here Fill Cells.xlsx
VBA Macro To Loop Text To Columns Through All Rows Of Data?
Apr 17, 2014I am trying to get a macro to run in excel that takes a simple text to columns command in one line of data and runs the command on a loop through however many rows of data there happen to be.
I've attached two screenshots - one with what I've got now (Before.jpg) and what I'd like to have after the macro runs (After.jpg). The code below is what I used to get the first text to column breakout, which I can hopefully run on a loop to breakout anything in the DEPT column that contains a "/". It can ignore the rows that only have one department to begin with.
Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Eventually I'll want to create another macro that transposes the breakout text back into the DEPT column and populates it with the corresponding data, but I figure I'll take things one step at a time.
Attached Images
Before.jpg‎
ter.jpg‎
Macro To Copy / Transpose And Paste Columns To Rows
May 1, 2013I'm trying to get a macro together that will take a set of workbooks that I've merged (using Ron de Bruin's RDBMerge add-in) and transpose all columns from B to HB into rows. Now, I know that each spreadsheet is 210 columns and 244 rows large and they are concatenated on one another. Attached is a brief example of what I am trying to go from and what I am trying to get to.
View 1 Replies View RelatedMacro For Hiding Rows Containing Empty Cells Through Different Columns
Sep 4, 2013I created a macro that analyzes some datasets and according to different parameters it puts an X at the side of every cell that goes out of parameters. These Xs are all in columns named the same way for it to be easier to find. What I want to do is to write some macro that will hide every row where the data did not go out of parameters to be able to easily see where it did. In the following example it would need to hide the second row since there are no Xs in that row. One of the problems I'm having is that the amount of columns and rows is variable.
Data
Data
Functional Upset
Data
Data
Functional Upset
#
#
X
#
#
[Code] .......
Below is the piece of code that I created to try to do this (G has the value for the amount of rows). I think it is working but it is either taking too long since it has to sometimes go through over 20k lines as much times as it needs to to cover all the rows that contain Xs or its getting stuck for some reason.
Code:
Worksheets(2).Range("I3:I" & G).EntireRow.Hidden = True
K = 0
Do Until Worksheets(2).Range("I1").Offset(0, K) = ""
If Worksheets(2).Range("I1").Offset(0, K) = "Functional Upset" Then
[Code] ......
Changing Existing Macro To Transpose Rows To Columns
Feb 22, 2008I have the following macro which I use to transpose a number of columns in one sheet to rows in another sheet.
PHP
Sub task1()Dim i As Integer, n As LongFor i = 5 To Columns.Count Step 2 n = n + 1 With Sheets("Working_Checklist_1").Cells(10, i).Resize(16) Sheets("Summary_Intermediate").Cells(n + 2, "c").Resize(.Columns.Count, .Rows.Count) _ .FormulaArray = "=if(transpose(Working_Checklist_1!" & .Address & ")=0,"""",transpose(Working_Checklist_1!" & .Address & "))" End WithNextEnd Sub
I'd like to change this macro so to transpose from rows to columns. I've tried a couple of things, but can't quite get it to work.
I'd like to transpose every second cell starting from E7 to IV7 in Working_Checklist into column BU in Summary_Intermediate, starting from BU3.
Change Multiple Columns Into Multiple Rows
Oct 10, 2008I have a large spreadsheet that contains and employee number and then up to 5 entries of Dependants (each has its own column). What I need to do is have 1 for each of the dependant information instead of 5 and have multiple rows for the employee if they have more than one dependant.
At the moment I am sorting the data by dep 1 name, pasting the data into a new sheet, deleting all the dep 1 stuff, sorting by dep name 2 and pasting that.........etc.
It doesn’t take too long to do but I just wondered if there might be a quicker way as this will be something I will need to do more often.
Macro To Copy Data From Specific Columns And Rows To New Worksheet?
Apr 29, 2014What I am trying to do is to look at specific columns, then copy the data in that column from specific rows from sheet 1 (named TIA) to sheet Macro1. I think uploading a sample of the spreadsheet would be useful.
Unfortunately the spreadsheet is a living document and continues to grow in both column and rows.. The data extracted at this point is from row 7, 23-60 and copied into the new worksheet starting at A1.
Requirements:
1) Row 5 states the macro the column will be associated with. There can be more that one macro associated to a column.
2) When column is found, data from row 7 column (x) will be copied to sheet macro1 EX. If Cell G5 = macro1 then copy data from G7 to sheet macro1 at A1
3) When column is found, data from row 23 column (x) will be copied to sheet macro1
4) repeat requirement 3 till no more Scenario's
Excel 2010 :: Macro - Move Data From Rows To Columns
Dec 13, 2011I am using Excel 2010 and need a macro that can convert data from rows to columns. I have read several posts about this subject but have no experience with macros and don't know how to change the macros to fit my scenario.
Here is what I currently have:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008........FL
11111......2000........FL
12121......1999........GA
33222......2000........AL
33222......2011........AL
33222......2001........MS
Here is what I need it to look like:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008.......FL.......2000........FL
12121......1999.......GA
33222......2000.......AL.......2011........AL......2001.......MS
There are up to 4 vehicles/locations per account number, and I need 1 account number per row (the dots above are for spacing only and not part of the actual data).
I could do this manually but because I have so many rows of data it could take days or weeks. Is there a macro out there that can do this??
Vb Code Macro Needed To Transpose Data From Columns To Rows
Oct 14, 2008i have the following spreadsheet with dummy data however, there is a before and after scenario i have posted is this possible with a macro ...
View 9 Replies View RelatedVBA Macro To Filter Rows By Cell Value & Hide Blank Columns
Oct 1, 2008i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.
basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.
the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.
where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.
Macro: Create Named Ranges. Unknown Columns/Rows
Sep 9, 2006I need to make named ranges from an unknown number of columns(at least 1) each with an unknown number of rows. Each column has the name of the named range as the first row, and then a variable number of rows containing part numbers.
I can do it 1 by 1, but id rather do it in a loop so that blanks dont cause errors. there will be different people using versions of this sheet with different model/part number information What i've tried: Count number of colums with row 1 containing data (11 max, which is more than will ever be used) add into array(I know i dont really need to add into the array, but i might use it later for some other code). The problem i'm having is finding the range of rows that need added to the named dynamic range and adding it.
modelcount = Range("G7") 'G7 (for now) contains =COUNTA(H1,I1,J1,etc)
For i = 1 To modelcount
Redim Preserve Models(0 To i)
Models(i) = Cells(1, i + 7)
Range1 = Cells(2, i + 7).Address(xlA1)
lastRow = Cells(rows.Count, i + 7).End(xlUp).Row
Range2 = Cells(lastRow, i + 7).Address(xlA1)
Reference = Cells(2, i + 7).Address(xlA1)
ThisWorkbook.Names.Add Name:=Models(i), _
RefersTo:="=OFFSET(Reference,0,0,counta(Range1:Range2),1)", Visible:=True
Next i
This gets me the range i need, but doesnt create the named range properly. If i go to insert>names>define, the named ranges are created, but they dont relate to the data in any columns. It shows the variable names rather than the cell range the variable represents.
Macro To Copy Data From Columns And Paste In Rows After Given Criteria Matches?
Nov 21, 2011I have a macro that would check data in Column A and validate if a particular number is repeating, then for that number go to column B, Take the Values from there go to a new sheet and paste the values in a row.
CurrencyDateRef CodeIDAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD-111,131.450Distr Payable 07152011USD07152011XDVU4315420.4240.C5001.USD18,606,772.190Distr Payable 07152011USD07152011XDVU4315420.4240.C5002.USD111,131.450Distr Payable 07152011
I get the data in the below format
CurrencyDateRef CodeIDAccountAccountAmountDes.USD07152011XDVU4315210.4200.C5001.USD420.4240.C5001.USD-18,606,772.190Distr Payable 07152011USD07152011XDVU4315210.4200.C5002.USD420.4240.C5002.USD-111,131.450Distr Payable 07152011
I need to the macro to get the data not from the second cell.
Below is my macro
Sub test()
Dim idRange As Range, c As Range
Dim uniqueID As String
Dim destSht As Worksheet, sourceSheet As Worksheet
Dim r As Long
Dim i As Integer
Dim map As Object, key, item
[code]....
Transpose Multiple Columns To Rows Based On Match Via Macro Or Formula
Jul 10, 2008I have two columns of data as follows:
10:57:42 273
10:57:42 263
10:57:42 253
10:57:42 241
10:57:37 273
10:57:37 243
10:57:37 249
10:57:37 261
10:57:37 253
11:04:47 241
11:04:47 253
11:04:47 263
10:54:31 254
10:54:31 240
10:54:31 265.......
VBA Macro To Delete Rows/Columns, Insert Formulas & Auto Fill
May 28, 2009I'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.
Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"
UserGRP_MAcro Macro
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Columns("B:E").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B1").Select
ActiveCell.FormulaR1C1 = "Existing userGroup"............................
Finding Macro That Will Combine / Consolidate Rows When Cells From 2 Separate Columns Match
Sep 26, 2013I need a macro that will combine/consolidate rows when cells from 2 separate columns match. example...
beginning:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 1064 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 80 unleaded 1101 100100
row 4 Seminole 30 dyed dsl 3421 100100
This is what I need the macro to do:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 2165 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 30 dyed dsl 3421 100100