I'm building a spreadsheet which includes a few columns which have activex check boxes which are linked to cells. I want to allow the users of the spreadsheet to insert new rows if necessary. Ideally, when these new rows are inserted, the check boxes would also automatically appear in the corresponding columns of the new row (and be linked to the relevant cells), just the same way that pull-down menus and formulas automatically copy into the new row. Is there a way to do this?
I have several sheets with about 250,000 rows per sheet.
But, even I sort by Column A, there are STILL hundreds or rows that are total blank interspersed down the page . . . I can't autofilter for blanks because there is too much data . .
For some reason I don't seem to be able to insert any columns/rows/cells in to my excel spreadsheet. This is a problem in both basis sheets and more advanced ones.
What is the VBA code for inserting text in all column B-cells of multiple selected rows?
I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.
I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.
As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.
I am trying to create a formula which will look at the data in columns A - I (50 rows) and copy this data to columns K - S but ignoring any blank cells. Thereby consolidating the data in the upper rows with no spaces.
I want to count the number of duplicate rows where the exact text in columns A and B match. An example is as follows, where column C would be the desired result. Note that there are hundreds of different text values of column A and hundreds of column B, I just simplified the example.
Looking for assistance in inserting 24 blank rows in between a column of names. And if not too much trouble have the ability to enter the number of blank rows to insert.
Before: Column A name1 name2 name3 etc.
After: name1 blank row blank row etc. name2 blank row blank row etc. name3 blank row blank row etc.
I am using MS Excel 2007 and I am trying to create a macro which will copy column E and paste the data in column E to the bottom of column D. However each week the amount of rows in these column with vary, they will always be the same amount of rows in column E as in column D but there may be 20 rows one week and a 100 rows the next.
The formula I currently have is below but this will only work for a specified number of rows. How I could change this to work for any number of rows?
Code: Sub IPT() ' ' IPT Macro ' ' Range("E1").Select
I would like to know how can i insert a blank row in between ACC# when ever a new series of data begins
name Acc # Balance Date John 2222 200 june 2013 John 2222 344 june 2013 John 2222 700 Sept 2013 Silva 4444 333 June 2013 Silva 4444 333 June 2013 Silva 4444 333 June 2013 Raff 5555 897 June 2015 Raff 5555 897 June 2015 Raff 5555 897 June 2015 Raff 5555 897 June 2015
I've got a spreadsheet that I prepare for a group that has patient data subtotalled by physicians. The problem is that the group uses this data for data entry and wants a blank row between each different patient name within the physicians subtotals and I have no clue how to get there. Any chance there might be some VBA code that could accomplish this?
I am having difficulty writing code to insert a hyperlink from a command button on a userform. I have set up a worksheet to be a data base for a lot of new files which are being created on a daily basis. The userform when activated asks the user to input certain information about the new file being stored in the data base (ie file name, date created, relevant project, description, who created it, etc). I have put a command button on the userform which I would like to activate the "Insert Hyperlink" menu so the user can then navigate to the location of the file being stored and have that hyperlink stored in a cell against the file when the "Submit" button is clicked on the userform.
I have read almost every thread on inserting hyperlinks with vba but none seem related to my needs. Using Windows 7 and Excel 2007
1. to leave a blank line inbwteen Shop A's Apples, Shop A's Pears etc
2. I also wanted to total up the price totals....(see example below).So if the last price was in E4, the total of all those prices above would show in F5 ....
I am wanting to create a validation rule so when two cells are added the together the answer must be less than or equal to 14:00 - otherwise a message box will appear
So for example if H7+I7 gives an answer of 13:00 in J7, that's OK, but if the answer is 15:00, the error box will appear. I know that data validation doesnt work on a cell that already has a formula so I'm hoping to use K7 for the Validation and then hide the column
I want to count the number of cells in a range that have text in them (any text at all) but not count them if they have numbers in them or are blank. How would this formula be written?
I am working on formatting a spreadsheet report where the values will change in column A. Here is what I would like to do via a Macro. Compare the cells in column A (e.g., compare A2 to A3, compare A3 to A4, and so on). If the values between the two cells in column A are different, insert three blank rows and set the active cell to the next cell following the blank lines. Example:
if cell A5 is different from A6, insert three blank rows below row 5 and new active cell is now A9 and the comparison would start again. I have been trying to code the macro for this but with no success. Here is the macro I have been working on.
Sub Macro1() Const NumRow As Integer = 3 Dim StartCell As Range Dim RowNR, NewCnt As Long Dim RowCount As Long Dim Count As Long Dim intRow As Integer Dim bFmtComplete As Boolean RowCount = Application.WorksheetFunction.CountA _ (Range("A1", Range("A" & Rows.Count).End(xlUp))) bFmtComplete = False RowNR = 2 Range("A1:J1").Select ' Rows("1:1").Select Selection.Copy................
I have a workfile, containing dates in column C and values in column G.
The data is imported. In most instances there are no dates in line with the second value. I need VBA code that will insert a the same date in line with the first value below the cell where there is a date....
appending excel sheet number in a formula. Here is the example.
In an excel book, I have sheets 1 to 50. In each sheet i have a standard format to enter the details. And in one sheet I want to keep some summary with required cells. I made a table by applying formula, for example in cell A1 i kept formula ='9'!$D$9. In this '9' refers sheet9. In the similar way i want to capture the data from all sheets.
Is there any formula to append the Sheet number automatically (like dragging the shell) intead changing sheet number manually. because it is wasting so much time.
I am trying to insert a blank row into a table (created using the table function in Excel 2010) without the copying the formulas. Every time I insert a row the the formula is copied.
I need to write a macro which inserts rows such that each ID has exactly 10 rows (1 for each brand). The difficulty is that each ID has a varying number of blank rows required.
The below piece of code carries out a vlookup on a defined cells value and produces a result in sheet one, however if the column index number in sheet 2 (Database) is empty the result 00/01/1900 is produced.
I'm not sure how to say leave the result blank if the column index number is blank.
I have problems inserting new rows in selected files. The files with the problems have a macro. I don't know if the problem can lie within the macro. There is no error message, it just won't insert the new row(s).
I've been looking for a free checkbook register template that will automatically calculate the balance as you add or subtract various amounts.
I found one which seemed to be just what I wanted but once I had reached the end of the rows for the worksheet and tried to add more rows the automatic feature quit working.
I found another Checkbook Register which seemed even better than the first one as it had a notation at the end of the rows.
It said to " Insert new rows above this point" and it had the last row greyed out.
I tried adding a new row and tested it out by adding some amount but it did not continue with the automatic calculation either.
But I dont want it to count the cells if the entire row, within that cell, i.e. C6:AD6, is blank.
It should only count the blank cells within a row if there has been some data entered on that row..provided it has been entered within the specified range.