Keep Entire Rows Of Spreadsheet Together
Jan 4, 2013
I've got spreadsheet I use to control the inventory of my user equipment; who has it, what is it, where is it, etc.
I'd like to "lock" multiple consecutive cells of each row so that they never seperate. This is because they need to be together. However, periodically, I need to move, sort, or rearrange the sheet.
Is there a way I can "idiot proof" this so that these cells never come unlocked from each other?
There is no vba involved (primarily because I don't know how), and no other scripting either.
View 2 Replies
ADVERTISEMENT
Jul 28, 2014
I'm trying to autofill a series of rows (that are blank) with data from an above row. I want to autofill the row in its entirety, not just filling in blank cells.
For instance (assuming comma is a new column). Colors listed are just a data example. Space between commas indicates a blank cell:
142, RED, GREEN, , YELLOW, , BLACK, PURPLE
(blank row)
142, GREEN, RED, ,BLACK, , PINK, ,
(blank row)
(blank row)
(blank row)
154, YELLOW, BLACK, , GRAY, , PURPLE, RED
(blank row)
(blank row)
So rather than it just completing the task in one desired cell, it would complete the task over the entire spreadsheet. Data spread can be as far as row 500 and column BY, so you can see how a copy + paste or a drag would get monotonous.
Another small example data set:
1,1,1453,0,10,-35
(blank row)
(blank row)
0,0,1448,0, ,-35
(blank row)
1, ,1443,1,3,-36
1,2,1408,2,7, ,
(blank row)
(blank row)
(blank row)
1,2, ,2,7,-39
(blank row)
(blank row)
1,3,1344,1,10,31
And column A will always have data (unless the row is completely blank.
View 1 Replies
View Related
Sep 2, 2009
I'm trying to sort a spreadsheet that has 6 columns with 36 rows total. I want to alphabetize the entire spreadsheet, from A1 to F36 so that A1 is the first of the alphabet and F36 is the last. When I select all the data on the spreadsheet and click sort by ascending, it sorts the columns within themselves instead of within each other.
View 5 Replies
View Related
Feb 6, 2009
For simplicity, let's assume I have three columns, "Company," "Policy," and "Balance."
Example:
Company A Life $1
Company A Med $2
Company A Dent $3
Company A Disab $4
Company B Life $9
Company B Med $5
Company B Dent $7
Company B Disab $2
According to my post preview, the columns are running together, but I do have it arranged in three distinct columns. What I want to do is program a macro to do the following:
Every time the name of the company changes, I want Excel to insert two rows after the last of the group, and have a summation of the "Balance" column in the first inserted row.
This is what it should look like:
Company A Life $1
Company A Med $2
Company A Dent $3
Company A Disab $4
$10 <- (this should be directly under the $4, but I'm not good with forum languages)
Company B Life $9
Company B Med $5
Company B Dent $7
Company B Disab $2
$23 <- (same with this - should be directly under the $4)
View 3 Replies
View Related
Oct 21, 2012
I have a dropdown box containing text strings. How can I use the selection from a combo box as part of a formula?
So for example I have a spreadsheet that is set up using a search term 'media', I want to select something else from the list e.g 'arts' and I want all of the references to 'media' to turn into 'arts' in the formulas. Kind of like a mass find and replace.
View 1 Replies
View Related
Jul 13, 2012
I insert a button in spreadsheet, when I click this button a userform appears with place to input value. I would like to check the entire column (maybe < 100 rows), if there is number exceed the input, then disply that ROW in another sheet.
View 1 Replies
View Related
Jan 8, 2014
first need to look for "PR" in column G, if found need to cut the entire row and insert at the top of the same worksheet.
View 1 Replies
View Related
Jul 29, 2014
I'm wondering if it's possible to have a formula applied to an entire column without adding unnecessary length to the spreadsheet until someone is actually scrolling down and using more rows. I want formulas to be constantly present in two columns to automatically calculate values as they are entered into other cells in the same row, but ideally without the otherwise empty spreadsheet being X amount of rows long.
I initially had the formula dragged down 15,000 rows, but it would really be much better if it could start out as a smaller size and then expand as the user requires.
View 9 Replies
View Related
Feb 2, 2014
The below code compares the Data in a Field that must be set and collect the duplicate Values in a second Worksheet.
The thing I want it to copy the rows, when a duplicate is found in Col A. editing the code below:
Original Sheet:
"A" "B" "C" "D"
Teil1A11000
Teil1B21001
[Code]....
View 7 Replies
View Related
Jun 28, 2007
I have a sheet as shown in the picture below. If the cells in Column N has some values that are the result of some calculations. How ever if there are more than one of it, the entire row has to be deleted.
Eg:The value in Column N 816.323 has 3 rows below each other.Only one has to be there.There can be upto 10 values one after other in this way.
I tried to write a progrma by using loops and cehcking if ther is value in the cell,go to cell below and if there is value,delete the row in a loop of 10 times.But it didnt work
View 6 Replies
View Related
Jan 8, 2009
I have a list of projects in several rows and at the column "K" is the status of the project, according to the status of the projects if the status is completed I want cut this entire rows and paste to another sheet calls Projects_Completed by using a macro
View 9 Replies
View Related
Apr 22, 2012
Using excel's text to speech I've put together a basic spreadsheet.
[URL]
Code:
Function talkit(Speech)
Application.Speech.speak (Speech)
talkit = Speech
[Code]....
View 9 Replies
View Related
May 6, 2014
After running advanced filtering through VBA like that:
[Code] ........
Excel returns a couple of rows which fit the Criteria. How to display these rows in a textbox in the userform? (all I've managed to display in it is one cell).
View 6 Replies
View Related
Aug 29, 2007
I've been tasked with developing a macro that will generate schedules. For background, we're taking a QuickBooks Customer file and exporting it to be picked up by the macro. I've got that part working, but now I need to delete various rows based on certain criteria.
The spreadsheet has a field for Job Status which we use to define whether an account is under contract (2 in QuickBooks), or some other status. Basically what I need to do is delete the entire row of data where JobStatus (Column R) is anything but 2.
View 9 Replies
View Related
Mar 30, 2012
I came across this code that selects (and extends) cells between the active cell and the next cell with data.
Is it possible to modify this so that it selects the entire Rows ?
Code:
Sub Test()
If Selection.Select "" Then
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Else
Range(Selection, Selection.End(xlToRight)).Select
End If
End Sub
View 4 Replies
View Related
Dec 31, 2008
in a selected columnar range.
I want to write a fairly simple, fast macro to examine a selected range and simply delete the row for each blank cell that it finds in that range.
I have read around that loops aren't necessarily that fast and some of these ranges could be 1000s of rows long...
way to write something like this in VBA?
View 9 Replies
View Related
Feb 8, 2008
I've got a pivot table that shows different activities their cost, budget and percentage complete amongst other things. I would like to use conditional formatting to turn the font of a whole row grey if that activity is a 100% complete (exactly more then 99,5% complete in this case). I can do this with conditional formatting by selecting the individual rows and then setting the conditions. Example: selected row =$45:$45, Formatting condition =$J$45>99,5%.
However I've got 1500 rows to do....how can I set this condition for all rows in one go?
View 2 Replies
View Related
Dec 5, 2008
I have a project that needs to be done today. What I'm trying to do is hide an area of a worksheet using a button click, then display that area when the button is clicked again. This area is approximately the lower right quadrant of the worksheet, so it shares column and row information with other data that must remain visible. Is there any way to do this?
Alternatively, I was thinking about making a duplicate of the worksheet, but omitting the area I want to hide on the duplicate sheet. The button would then hide and unhide the two worksheets, hopefully making it have the same effect as hiding or unhiding just that area. If I do this, I would need to maintain the exact same data in the visible area of the two sheets.
View 13 Replies
View Related
Jan 23, 2009
What I would like to do is do a summary sheet that does a little math. I can make it add but only one row at a time. Here is the general idea...
The fields of the summary sheet are 'PromoCode', 'AdvertisingCosts', 'Labor' for example. And I want to add the currency fields (add, as in math addition) of 'AdvertisingCosts' and 'Labor'.
The fields are on another sheet. What I am hoping can be done is that all those fields for every row can be made to work without having to hard code each one.
Also I would like to have it update with any NEW rows that arise. Like if the existing rows were 'row1' and 'row2' and then some other time 'row3' is added that 'row3' automatically gets included on the summary sheet.
View 14 Replies
View Related
Aug 1, 2014
Here is my attachment with data in first sheet and expected output in second sheet. Have given comments in second sheet for better understanding.
Data is of a debt collection, in sheet column F "form #" denotes loan form numbers and column L "paid" denotes the amount received or not received.
In this Form # are unique and form number will repeat with different paid amount.
Entire row to be colored based on the sum value of paid column amount of respective form number.
1. non repeated single form number with negative value of paid - Orange color
2. repeated form numbers with sum of values in paid column as positive value - only entire row of form number with negative value in blue color
3. repeated form numbers with sum of values in paid column as negative value - first form number row to be colored with yellow and rest of repeated form numbers with purple color and its sum value (negative value should be made available in yellow line of the Form # group to the right of right most column data)
4. form numbers and paid column of value "0" - pink color
I tried recording and edited macro only for the logic of paid value "0" and confused with other logics and declaring variables...
View 9 Replies
View Related
Feb 25, 2014
I'm trying to put together a script which on the sheet "Resource" compares every cell in the range B:U, starting at row 8, and where all cells match, keep the first row, but delete the duplicate proceeding rows.
I've found the code below here: [URL] ...., which I thought I may be able to adapt, but for me to include all the columns in the 'If.Evaluate' section of code seems to perhaps not the most efficient way of doing this.
[Code] ......
View 5 Replies
View Related
Mar 16, 2014
I am trying to hide all rows and columns with zero balance in the cells with the use of a macro button.
View 8 Replies
View Related
Nov 24, 2011
I've got a report which is approx 40,000 rows long and I need to delete out alot of the info.
I need to delete out entire rows if column C contains a 0.
I've written a loop to do this but as theres so much data it take a long time to run.
Is there a smarter way to do this?
View 6 Replies
View Related
Apr 20, 2012
I need excel to delete entire rows if there are duplicates in two columns combined.
So, if I have this sample data (in reality I have a lot more columns)
1 Tr. ID Last Name First Name Sex
2 RBM_A Miller Johnny M
3 RBM_A Bayron Josephine F
4 RBM_A Jackson Reynaldo M
[Code] ........
I want excel to delete only row 9, not 11, 13 or 14.
So far, if I look for duplicates in both columns it marks duplicates in either one. Also if I delete the duplicates, it deletes them only in those two columns, leaving me with distorted data (in this example, row 14 would have no last or first name, but only the values "RBM_C" in column B and "F" in column E.
View 2 Replies
View Related
Oct 6, 2012
I have some data in column A I want to delete the entire row if the cell hasn't the character @
View 5 Replies
View Related
Oct 3, 2013
I have created a worksheet with 7 Tabs. 6 of the tabs are tables of existing parts and their catalog information. The Front Sheet I want to be a place where people can select data via a drop down or typed in, and in turn have that headsheet display the results of the search.
I have the 6 tabs broken down by part type, and figured those can easily enough be the first choice on my worksheet. So by picking that I can direct excel to which tab we want to run the search on, but ater that I'm drawing a blank.
For an example, say I'm looking for a Elbow that has a 6 size on one end. I would like to use the drop down to choose "Elbows" and then like to select/type 6 in the End 1 size box and have Excel show me all the reslts from the Elbow Tab that match that criteria.
View 4 Replies
View Related
Dec 17, 2007
I have a long list (over 1000 lines) of numbers, some of which are duplicated. Up to now I have been sorting them, adding in a simple check column to see if the number is a duplicate of the one below, and then manually deleting this cell.
Is there a better way to do this using VBA? The only way I have managed to find is by deleting the entire row, but I cannot do this as I have additional data to the right that I need to keep.
I also cannot install any add-ons as this on a work computer.
View 9 Replies
View Related
Nov 21, 2007
Excel Range A1 - D500. Data located in column C. I would like to delete the rows in this spread sheet that do not contain any of the following 3 prefixes of part numbers from column C: X-SXP, X-SCM and X-SBP. There are many part numbers but only parts with these 3 prefixes are needed. Part numbers are 10 characters long after the prefix. If any additional information is needed, let me know.
I viewed the Delete Rows Meeting Conditions thread, but the input value in that code is for what you want to delete, and I need to delete everything not meeting the data I want to keep, which is about 30 different part numbers. Also, that appeared to only offer one input and not 3.
View 3 Replies
View Related
Jul 15, 2013
I have a large set of data regarding the activities going on in particular rooms. The data contains activity name, start time, day, duration, room and size.
I would like to select all rows that contain a cell in the activity name column which contain the words 'VideoConference'.
I have tried to Search, Select All then index the remaining data using =iferror(index(etc. However some activities occur in more than one room and all variables apart from 'Room' are the same. Thus when I have tried to index the room column I get the same room for both rows.
ps the raw data set consists of +28000 rows so I can not simply look through and copy.
View 4 Replies
View Related
Mar 5, 2009
I'd like help in creating a macro that deletes an entire row that has emtpy cells in col B, C & D in the same row.
So for example if I have empty cells in b3,c3 & d3 I'd like the row deleted.
I've used the code below for just column B but I need to include column C & D as well. I tried putting Columns("B:D") but it deletes everything.
View 14 Replies
View Related
