Swapping the contents of 2 rows, my code does the operation but it is very slow. Here is my code
Sub swap()
Dim aTemp() As Variant
Dim swap As Boolean
swap = False
Dim row As Integer
row = 19
[Code] .......
I would like to insert a form control (up and down arrows) into a specfied range of cells in a column - ideally the first column. When the form control up or down arrow of the assigned row is clicked it moves that row up or down essentially swapping positions with the row directly above or below. I've seen this functionality in custom excel applications but my searches for code on this topic have been fruitless.
View 5 Replies View RelatedI have a list in Excel, and it has the company in one column, and it's information in the next x rows until there is a blank row (4-7 rows). I get that you can copy the rows under the Company, transpose next to the company, and then delete the contents of the cells that you just took the data from, but I have 6200 rows do to this to, for 500+ companies. I tried recording a macro for two entries, and this is what I have (see below), but how do I modify this so that I don't need to type in every single range, and it will do it to the whole column? The data is in column A, I am posting in column B.
Basics for Macro Requirement:
1. Find the blank cell in row A
2. Skip the next cell/row (this is the company)
3. Select all the cells beneath the company cell, until it reaches the blank cell underneath
4. Copy, transpose these cells next to the Company cell (transpose in column B)
5. Delete the contents in row A that were just transposed
6. Find the next cell with data (company)
7. Repeat Steps 2-6
My recorded Macro:
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("A3:A8").Select
[code]....
I have a huge xls of names setup as Last Name, First Name in a single column.
Any formula or somehow to change the entire column to read First Name Last Name. No comma.
Example -
Smith, Joe
Brown, Barry
-------------------------
Output desired -
Joe Smith
Barry Brown
I am attaching a sample sheet as a reference.
in fact the sheet could contain hundreds if not thousands of rows.
Look at column Player 1, the last 3 characters are AoS.
I want to find any rows where Player 1 has those three characters ( AoS ) and swap them with the contents of the Player 2.
BUT if that swap happens then the following swaps must occur as well:
Contents of Column "Side 1" swaps with Contents of Colum "Side 2"
Contents of Column "Player 1 Pts" swaps with Contents of Column "Player 2 Pts"
Ultimately I want this extended so that is the letters AoS appear in any of the following columns ( Player 4, Player 6, Player 8, Player 10)
the following swaps happens:
Contents of Column "Player 3" swaps with Contents of Column "Player 4"
Contents of Column "Side 3" swaps with Contents of Column "Side 4"
Contents of Column "Player 3 Pts" swaps with Contents of Column "Player 4 Pts"
Contents of Column "Player 5" swaps with Contents of Column "Player 6"
Contents of Column "Side 5" swaps with Contents of Column "Side 6"
Contents of Column "Player 5 Pts" swaps with Contents of Column "Player 6 Pts"
I want to swap values of two cells. can anyone help me on that? Eg. Cell A1
contains the value 20 & cell A2 contains the value 100. I want 100 on A1 &
20 on A2.
I have column AD where there are something like 2,500+ formulas. I would like to change the columns used in the formulas around. By that I mean the first formula is...
Code:
=SUM(AA6-X6)
...and I would like it to be...
Code:
=SUM(X6-AA6)
Now the thing is, the formulas are not continuous, there are breaks where there is a formula to sum say the previous dozen or so for example, then it skips a couple of rows and I have more formulas and then the sum say for the previous dozen for example and so on.
I just want those cells changed with the particular formulas as above.
I have let say column A and I have the date and name John 1913 all the way down to John 1940.
But what I need is a way to turn it around so A1 starts with John 1940 down to 1913.? If I drag down it counts up but I want it to count down if that makes sense.?
Have 5 columns (A1-E1) with different bond data: (respectively)
Coupon, Maturity, Settlement, Price, Yield (asset swap is F1)
Depending on the type of swap I am performing i want the ASWAP (user-created) function to reference different values within the row.
For example: Swap 1 = settlement, maturity, coupon, price, curve
(curve is a user input single number 1-3)
Swap 2 = settlement, maturity, yield, price, curve (yield in place of coup)
Swap 3 = settlement, maturity, yield, 100, curve (100 for par redemption)
etc. I won't type out all the iterations, not necessary.
The way that I want the ASWAP to recognize which Swap# to use is by inputting a number into a cell beside the Aswap column that I can input 1, 2, 3, etc., and then recalculate to get the desired outcome. Want to keep this a static reference as well as the price at par (each owning their own single cell, not a fill down the columns because there are too many).
So assuming G1 is the Swap# input, and H1 is 100...
How do i write a formula that is capable of reading the input in G1 and outputting the proper swap calc.
My gut feeling was along the lines of nested IF's with the corresponding formula... but i won't mess around in futility on here.
Let me know if this is too confusing without me putting up an example.
Right now I have 2 ranges say A and B. Range A is in a worksheet on row 7 and range B is on the same worksheet row 8. What I want to do is make it so that range B gets pasted in for range A and vice versa so that range A in now in row 8 and range B is in row 7.
View 2 Replies View RelatedSwap the data between two columns at a time. Here are multiple columns to be swaped in one worksheet and I have many worksheets to work with.
I am trying to chart a range of dates, for example. I have some boats that are on contract between dates a and b. I would liketo plot several boats with their contract date range. I have tried using a bar chart, but all the lines start at 0. I got it working using an Excel 2007 Stock chart, however, it is in the wrong orientation. I would like to Rotate the Chart 90 degrees, swap the axis, or find a better method all together. I cannot post the spreadsheet as its confidential.
View 2 Replies View RelatedMy pivot table shows Towns as the Row headings and Month as the column headings. When I do a Chart, I get Towns on the X axis and Months as Y. I want the chart to show all my May data in a stacked column, then all my June data in another stacked column, etc. If I pick "Select Source Data" the options are greyed out. What's a mother to do?
View 1 Replies View RelatedI have an interactive chart displaying stacked area charts for 2006 and 2007
I currently have 2006 in front of 2007, but as the user chooses 1 of 20 units, it will look better to move 2007 in front of 2006
I am using the code below to "swap" the selected cell values in a column with the ones to the right of the selection. It works fine on unfiltered sheet, but if I apply an Autofilter, it copies both visible and hidden cells, and I only want to swap visible cells. What to modify?
[Code] .....
If a row in Column AF does not say “Cust” than I want to delete it, next if a row in Column A does not say “Numb” I want to delete it.
View 3 Replies View RelatedI'm trying to put together functionality which allows the user to remove cell content within a selected row. I've chosen to remove the cell content rather than to delete the whole row because I need to maintain the 'Input Range'.
The code below is the script which I've put together to clear the cell contents.
Code:
Sub DelRow()
Dim msg
Sheets("Input").Protect "password", UserInterFaceOnly:=True
Application.EnableCancelKey = xlDisabled
Application.EnableEvents = False
msg = MsgBox("Are you sure you want to delete this row?", vbYesNo)
[Code] ........
The code works fine if the user selects one row. However, if the user selects mutliple rows, although the text values are removed from all rows, only the 'Interior Color' is removed from the first rather than all and I'm not sure why.
I'm trying to split the contents of a cell to a new row (with data from other columns on the row copying across) below the originating cells row. Most of the split cell macro posts I've seen split the contents to a new row at the bottom of a range, however I'm looking to split the contents to a new row directly beneath the original row where data will already exist. I don't want to copy over this data I just want to push the data down depending on the number of rows created.
The contents in the cell being split can vary (1:n), so the number of rows needed to be inserted depends on the count. The majority of the contents in the cell is separated by a line feed (Alt Enter) but some may be separated by a tab or comma.
Essentially what I'm trying to achieve is something from this
A
B
C
D
[Code]....
I'm trying to sort a whole list of data that's been separated into blocks with a row containing the value "a" like:
row 9 a
row 10 455
row 11 8892
row 12 34
row 13 a
row 14 578
row 15 92
row 16 a
etc..
I want to sort each block into ascending order using VBA code and then add in some extra code to do some more analysis.
The data to be sorted is in Column D, and there are also values in Column C and B that need to be sorted with the Column D values (so that they stay in the same rows).
Which formula should I use if I want to sum a column without including content from hide rows in the same column.
a2 is hided so I need the sum = 6
1
9
1
2
2
I'm attempting to FIND all occurrences of the word Godaddy embedded in strings in Col A, and if found, copy the contents of Col B to Col E.
1. The VBA is amending on the Loop While line, and
2. What syntax do I use to copy the contents
HTML Code:Â
Sub Macro3()
'
' Macro3 Macro
'
'
With Worksheets(1).Range("a1:a10")
[Code] .....
Small piece of code to clear the contents of
Rows 33 thru 100 , then
Rows 142 thru 209 , then
Rows 251 thru 318 , then upto
Rows 43524 thru 43591 then finish
Every week I get a report that contains 1 or more (sub) part numbers in each row down column E, separated by a space. I need each part on a separate line, with the rest of the data on that row intact. Because these are the sub-components of an overall part, the data in column E is stored grouped together like that.
I currently use text to columns to break those up, separating them by the space, add a count of those cells so I know how many sub part numbers there are, run a 'duplicate rows' macro on that count, then manually delete out numbers so I'm left with one part number per row. The report is already over 100 rows long, so after I run the 'duplicate rows' macro I have a lot of rows to work through.
I am working on some code that I would like the user to be able to re-run should the underlying data in the adjacent tabs change. My existing code is shown below
Code:
Option Explicit
Dim wb As Workbook
Dim ws As Worksheet
[Code]....
I'd like to be able to remove all data on the ALL tab so that when the UsedRange.Rows.Count is run again on that page it will only see the header row. In the past I've deleted or cleared data from the sheet and the Rows.Count still counts the rows which have no content in them.
I have data in Col A. and i want to split (50 characters) in each row.
I know the formula but dragging formula to following rows is not working.
I have to write the formula manually. I write in b1 B2 and B3 as follows.
I want following
=MID(A1,1,50)
=MID(B1,251,50)
=MID(C1,501,50)
When i drag down the formula to bottom rows it repeat
Desire Formula in COL B
aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
=MID(A1,1,50)
aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
=MID(B2,251,50)
[code]....
I want to copy down the formula to 10k rows. When i select b2 and b3 and drag down i get following results.
aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
=MID(A1,1,50)
aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
=MID(B2,251,50)
aaa asdf 242424 alkjsaf 32234 asfasf 23234 dfdfdfd
[code]....
I have a spreadsheet with a serial number is row 1 in column A with the rest of Row 1 empty. Column A is empty in Row 2, but has the data associated with the row above in columns B through L of Row 2. Then comes 2 blank rows and the pattern repeats with a new serial number in column A of row 5 and so on. I would like to move the serial number down one row, delete the now empty first row, delete the following 2 empty rows, and then loop to do the same thing again for all 9000 rows of the spreadsheet.
View 2 Replies View RelatedWhich formula should I use if I want to sum a column without including content from hide rows in the same column.
a2 is hided so I need the sum = 6
1
9
1
2
2
I have merged two workbooks into one. What I need to do at this point is to delete all rows that have a duplicate entry, basically anytime the cell content in one cell matches the cell content in the cell right below or above it, BOTH rows should be deleted. At this point, this is above my VBA skills so I'm asking for help in how to do this. The stripped version of the workbook is attached (only 100 rows) but in reality this is a huge workbook with almost 22,000 rows.
You will notice in the attached workbook, that cell contents for A2 and A3 match. For what I need to do, I need both rows (2 and 3) to be deleted. If you go down a bit, starting in row 89 all the contents in column A are unique so those need to remain.
I have a spreadsheet containing 100 "requests" which can be made by a user
i would like the option for the user to be able to delete a request should they wish it either by deleting a row or clearing the contents
Currently I have a single column which is full of buttons (one for each row)
when i click the button on row 10 I would like row 10 deleted
when i click the button on row 30 i would like row 30 to be deleted etc
is there a way to do this without writing 100 macros which are specific to each line?
I'm am trying to set up a household finances dashboard in excel - What i'd like to do is categorise & tag HH expenditure based on part of the description within a bank statement, the desired result being:
account tagging.jpg
My ideal solution would be some kind of lookup with a table containing the keywords & corresponding tags to match part of the description to, as this will allow easy management of the tags, situations where I have multiple keywords for the same tage (supermakets for example), and so forth. Is this possible?
Currently I am using a very large nested forumula which i've cobbled together based on searching for ideas. It works, but managing new tags is difficult, part of the formula i'm using is:
=IF(ISNUMBER(FIND("00111888",'JOBSFPCUR-20140414'!C4)),"Pete transfer",(IF(ISNUMBER(FIND("LAURA",'JOBSFPCUR-20140414'!C4)),"Laura Pocket Money",(IF(ISNUMBER(FIND("TESCO",'JOBSFPCUR-20140414'!C4)),"Supermarket")))))