is there a way to stick a few row of cells together so that when i move one they all move.. i have some rows that when i sort them i want them to move together with the other ones..
View 9 RepliesI am trying to move the data in a row from cell D thru I (eye) to cell H thru M.
Simply move it all over a few cells. Well not so simply.
The line in RED fails.
I have rows of data starting in row 4.
I have row Headers taking up columns A:G.
My data, which is downloaded, can be anywhere between H4 and IV900.
I need a macro that will go to G4
Then do the equivalent of arrow right to the first cell containing data
Select that cell and all cells to the right of it
Copy the selected range
Cut and Paste it back to H4
Move to G5 and repeat all the way down the worksheet so that I end up with al of the data starting in column H rather than spread all over the worksheet.
I have an Excel sheet with several columns of data, each containing the link to a website. To the far right of this data, I have a column called "Official Source" in which I'd like to copy over any website links containing the phrase "sec.gov" (if there are any, if not than leave the cell blank).
For example, if I have the following data in my sheet:
Column A Column B Column C
[URL].... [URL].... sec.gov/Ar
Than I would want the last source to move to column D -
Column A Column B Column C Column D
[URL].... [URL].... sec.gov/Ar
how to do this?
The attached workbook has formulas that will return data from category sections that will grow and contract over time.
While it tells me that the total price of the "Active Overdue Customers to be Scheduled" section is 4119...I would like to determine how many in that section also have "Dunn" under the "Company" column.
Every section does not have the "Company" column, and "Company" is located in different columns in different sections.
I have whats going to be a very large "Input" worksheet that has several headings of columns. One of these columns is "category" as in parts of a vehicle, i.e. steering, wheels, rod-ends and so on. Now what I would like to do, is once I type a new row of information in, to have the filter go through and copy said category row into another worksheet where I can have all of them separated on there own.
Here is a picture of my current spreadsheet. So basically I want to be able once I save the file, that it goes through and where it detects Rear-End in "B18" I would like it to select row 18, copy all of its cells and move them into the worksheet Rear-End
I have a task that I need to perform every now and then. And even though I normally find my ways around in Excel, I can't find a working solution for this one.
I have a list of features that I need to match with another list and place them on the same rows in my sheet. The attached file contains 2 worksheets where "Before" shows my normal input, and "Wanted" shows what I need to do.
Cols A-E contains info for the first set of features, cols G and H is the cells I want to move. Please note that sometimes the G and H columns contains info that are not present in A-E, and then I need to move those features around as well. An example is present at the bottom of the "Wanted" sheet.
I have to move formulas from cells without the changes ?
Then i use copy-past it changed the rows and collums numbers, but i need to keep absolutly the same formulas
way to find all the cells in range A1:A200 that partly contain the string "FindThisWord", and then cut & paste each of those cells 1 row above and 2 columns to the right?
View 8 Replies View RelatedIn my current spreadsheet I use Offset to change the values of various cells. The problem is that if I make any changes to the columns it throws the whole offset thing off. So, instead of using offset to paste all the data is there an alternative that would work better?
I was thinking maybe I could name a column as a range and then have the cell in that row equal a value.
What solutions are there?
ActiveCell = TextBox1.Value
ActiveCell.Offset(0, 1) = TextBox2.Value
ActiveCell.Offset(0, 2) = ComboBox9.Value
ActiveCell.Offset(0, 3) = ComboBox2.Value
ActiveCell.Offset(0, 4) = ComboBox3.Value
ActiveCell.Offset(0, 5) = ComboBox11.Value
ActiveCell.Offset(0, 12) = ComboBox1.Value
ActiveCell.Offset(0, 13) = TextBox6.Value / 100
ActiveCell.Offset(0, 16) = TextBox4.Value
ActiveCell.Offset(0, 17) = TextBox5.Value
ActiveCell.Offset(0, 18) = CostBasis
ActiveCell.Offset(0, 19) = NumberOfShares
ActiveCell.Offset(0, 59) = DividendFreq
I have designed a form for my wife to use in school to help teach fractions and decimals.
The children will enter the numerator into a cell (say D2) and the denominator into another cell (say D6) and the decimal equivalent is then displayed to 2 decimal places in a third merged cell (say C8 - E8).
What I would like to do is this:
When the worksheet opens, the only cell that I want the children to be able to enter data into is the first cell (ie D2). When they enter a value (say 1 for the numerator) I'd like the return key to move the data entry to the next cell (which is D6). After this, when they have entered another value (say 3 - they are invesigating thirds for example), I'd like the return key to move to the next numerator cell (which is H2), then denominator cell (H6) and so on.
How do I do this? I have never created forms or macros and I suspect that this is how to do this.
For the first set of fractions that they are investigating, thirds, I'd like to limit their data entry in the denominator cells only (D6, H6, L6, P6, etc) to 3 and reject any other number. Later in the worksheet, they will be investigating quarters, fifths and so on.
How do I do this? I don't want then to use any list or drop-down, simply to type a value into the cell and it be accepted (if it is a 3 when investigating thirds) if it is correct or simply ignored if it isn't. Is this possible?
way, especially if it involves anything complicated like macros. Being active on lots of forums it is quite depressing when I see that someone has answered a poster's question but the answer seems to either assume a high level of knowledge or tends to assume that certain steps are taken without actually stating them.
I got a sumif formula, where I sum the hours if a certain person is represented. =SUMIF(K12:R12;"TW";K11:R11) Hours in row 11 and the person in row 12.
The patern in my excel document is that every second row is hours and persons. Hours on odd row number and persons on even.
My problem is that when I want to copy my formula down, it goes one row down to =SUMIF(K13:R13;"TW";K12:R12) I want it to go two rows down to =SUMIF(K14:R14;"TW";K13:R13)
Is there a way to automatically move cells down after I entered data in it? This way I do not have to scroll all the way to the bottom of the list to add new data.
View 5 Replies View RelatedI've been trying to figure out how to use a formula that would enable me to copy/copy & move a range of cells right one column if a certain criteria is met.
Basically, if a criteria is met, I want to 'copy' everything from a row to another row, but offset it by one column (so that what was in, let's say, a1 would be in b2).
I've been tinkering with index(), if() and offset() in various combinations but think I must be missing something.
I have come across a format issue I need to deal with it at work.
I have a list of data like this:
A
1234567
8956423
4561237
7531596
8524561
In Column A, I have about 2,000 lines of information. What we want to do at work is create this information to be held in one cell at the top of the excel and in numerical order.
Like this:
A
1234567, 4561237, 7531596, 8524561, 8956423.
In Sheet1 is 11 headings.
In column I, titled 'complete?' the user simply puts an x to show that the order is complete.
Once this happens, I would like the information in the row containing the x from columns A to I (but not J and K) to be copied to the next empty row in Sheet2. Then to clear the information from the cells in the row of Sheet1 from columns C to I, but have the data in columns A,B,J,K remain for the next order.
I have the following code that will go down through column "L", look for the value "ATS", and if found will move it to the right 1 cell. this works find,
My need is to also move the 2 cells directly below the cell containing "ATS", then clear the contents of the "Original" 3 cells before going to find the next instance of "ATS".
Sub Move_Values()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("L4", Range("L5000").End(xlUp))
For Each cell In myrange
If InStr(cell.Value, "ATS") > 0 Then
cell.Offset(0, 1).Value = cell.Value
cell.ClearContents
End If
Next cell
End Sub
I have 2 col A & F. F col associate with date values & Col A has some color cells.I want to move down those color cells to a selected non-color cell row when run a macro.
I have code which perform to move color cells down to selected cell but problem is, if i have A4 & A6 color cells and i selected F2 cell & run macro then color cell A4 & A6 move down to cell A2 but cell A3 move to place A6. The sequence is
A1A2A4A6A5A3A7A8 & so on but i want cell A3 just push down the order & sequence will be
A1A2A4A6A3A5A7A8 & so on My code is as
Code:
Public Sub Move_colored_cells_rows()
' Instead of moving the entire row , 10 columns will be moved
Const NUMBER_OF_COLUMNS = 10
[Code]....
Every day we receive a text report via email (only way it can be received), so we take this text report print it out and manually type it into an excell sheet.
I believe this is a waste of time. Is there any way I could take this info and automatically place in the the excell?? Until know I found a text to excell converter and was gonna create a macro to automatically convert this info into the excell report.
I need to move a portion of info over 4 columns. I need to do this after a certain criteria is met.
A B C D E
1 Mem# Mem Name Sta Area
2 123C Joe Smith G bev
3 4 Wine 4.95 6
4 5 wine 5.95 6
5 2 food 6.95 5
6 124G Fred Choi C Bev
7 4 Wine 4.95 6
8 5 wine 5.95 6
9 2 food 6.95 5
A3, B3,c3,d3 need to shift over 4 clmns then when nxt member #(a6) is met rows below need to shift over 4 clmns until nxt mem #, so on and so forth. Please Help. New to the macro scene.
There are a series of input cells in a protected worksheet and when the user hits an enter, tab or arrow key, the selection cycles through them in order. The cycle also restarts when the mouse clicks one of the cells out of order. After I switch worksheets and return to the first worksheet with the code, first it says there is a "Compile Error: Sub or Function Not Defined" and the code "Call SetKeys" in Sheet1 is hilighted. When I ignore that and stop the debugger, there is a "Run-time error '13' Type mismatch" and the code line "If arrIndex > 0 And arrIndex < UBound(arr) Then" is highlighted.
My question is: why doesn't the code work after I switch worksheets? especially given that it works perfectly before I switch worksheets. (ie the file opens on sheet 1 and everything is perfect, click sheet 2 tab and back to sheet 1 - nothing works) I also have yet to figure out why it seems to be necessary to post the same code in both ThisWorkbook and Sheet1.
Private Sub Workbook_Open()
'This code allows the user to change the unlocked cells, but protects each worksheet
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="mypassword", _
UserInterFaceOnly:=True
Next wSheet............................
I have a very large spreadsheet that I imported from a Text File into .xls. I need help changing some of the cells around. there is a macro that change the format into the desired look. It takes me about 4 hours a week to clean up this spreadsheet. This is what is the spreadsheet looks like after importing:
Row 1Column AColumn B
Row 2340 Name
Row 3
Row 4
Row 5Actual
Row 6Activities
Row 7AAAAAA16:34
Row 8BBBBBB35:08
Row 9CCCCCC0:02
Row 10DDDDDD57:25
Row 11EEEEEE8:56.....................
I would like to move the data in one column up by 1/2 a row without moving the other columns/rows in the spreadsheet
Here is an example I made quickly in Photoshop - the above image is what I currently have in Excel, and the lower image is what I would like to achieve, with the column shift that I would like circled in red
(the empty space under the column heading is just an artifact from the edit, it doesn't have to be there)
I have 2 large rows of data next to each other imported from a different programme. Some of the cells in these 2 rows consist of values, some consist of words. I only want to cut the cells with values in these rows and paste them in a new row, called TXid. I have written VB code that cuts all the data in the rows and pastes them onto the new row. Now I would like to cut only the values in the row. How do I select only the values in the cell?
Cells.Select
Cells.EntireColumn.AutoFit
Range("I1").Select
ActiveCell.FormulaR1C1 = "TXid"
Range("G3").Select
Do
If ActiveCell = IsNumber = True Then
Selection.Cut Selection.Offset(0, 2)
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = ""
Range("H3").Select
Do
If ActiveCell = Isnumber = True Then
Selection.Cut Selection.Offset(0, 1)
Loop Until ActiveCell = ""
The ActiveCell = Isnumber = true is not working the way I want it to.
I have a cell comment on a series of columns and want to show only one column at a
time. For this reason I hide the ones not used, but the cellcomment doesnt move and
is still at the original place. How I move the comment when the original colums between are hidden ?
I have an excel file that contains data from bank transactions.
In column A at irregular intervals is a cell with " User Group:" It depends on how many transactions there are in a batch, could be 1 or up to say 200 as to when the next "User Group" cell is found.
After that also in column A is "Item" then "Lodgement Ref" then at the end "Batch Totals"
i.e.
User Group:
Item
Lodgement Ref
Batch Totals:
User Group:
Item
Lodgement Ref
Lodgement Ref
Lodgement Ref
Batch Totals:
I want to find each instance of "Item" then move that cell and every cell over to column CK down to and including "Batch Totals" i.e. A3401 to CK3410 to CM 3400 so it lines up with the top of the transaction block.
There are over 60,000 lines so it's worth trying to find a solution as I can then use the methodology to make other refinements in the spreadsheet.
I have two sheets and need information from Sheet titled Add-Del_chng positions to be moved to the sheet titled Cost Distribution.The issues here are :
1. The values in Cells A through J should be moved only if col Z value is 'hard coded"
2. The values should be moved to the 13th row.
3. This should happen every time the value is input into col Z of sheet titled add_del_chng.
Currently the macro does not run unless I go into the code and press play. I need this to happen automatically.
I'm working with a large master list of part numbers, and matching data to these numbers from a varying data table via vlookup. I have to vlookup to the actual part number, (13,000 numbers), then subtotal the data based on the "Rollup" part number. (eg: Two different old PNs have now "rolled up" into a single part number. I am pulling sales data from all of the old numbers, and subtotaling the sales to the new number.) Too much information.
Here's my issue. After subtotaling on the rollup number, I want to copy and paste ONLY the rollup numbers and associated data to a different sheet. This should be easily accomplished by going to level 2 of the subtotals page, hiding all the old PNs and their data, selecting the remaining visible cells, then "Go To", "Special", "Visible cells". Normally, you could copy only the visible cells, and paste them wherever you want. But because of the total length of the subtotaled worksheet, (nearly 25,000 rows), I get the error message, "Micrsoft Excel cannot create or use the data range reference because it is too complex."
You can overcome this by selecting smaller ranges of data, and copying and pasting a little at a time, but since the "visible rows" will vary, I can't figure a way to incorporate this into a macro. I've tried setting specific ranges in the macro, and copying and pasting fewer cell at a time, but if the range named in the macro is a hidden cell, the macro won't select any of them.
Here's an example of what I've got so far. I realize it's not the most efficient code, but I'm working with limited knowledge and ability.
I have a spreadsheet as per the enclosed attachment. The top date is always Today's date, regardless of when the spreadsheet is opened. The rest of the dates are Today's date plus 1, plus 2, plus3, etc. Therefore, the dates are moving up the table, day by day. The Saturdays are linked to the correct dates by using the =IF(WEEKDAY(F6)=7,"Saturday", "") function, so they are moving up the screen as well.
The horizantal lines between the weeks move correctly due to Conditional Formatting (thanks to bpeltzer for that tip).
I would like to merge the blocks of cells that make up a week in such a way that the merged group of cells moves up the table correctly with their corrosponding dates.
I have manually merged the top row of cells in the attachment to indicate what I intend to do, but tomorrow, when the dates have moved up one, it will look all wrong again.
Is what I want to do possible, and if so, how is it done?
I have 1 column with a varying amount of rows (around 2000+ rows) with data that is about 10 chars long. Since I cannot print it all on a few pages, I would like a way to re-orgnize the data into cells so I can print on as few pages as possible such that the qty of columns isn't wide then an 8.5 x 11 page.
One idea is the following.
Here's what i have.
Col1
1
2
3
4
5
6
7
8
9
10
...
Here's what i want.
Col1 Col2 Col3 Col4 Col5 Col6 Col7 ...
1 2 3 4 5 6 7
8 9 10 ...