I've a sheet and in row A is a list of tasks to do like:
A1 - Task1
A2 - Task2
A3 - ...
Is it possible in VBA to do this:
A1 - Task1
A2 - Blank cell
A3 - Task3
A4 - Blank Cell
A5 - ....
in other words to input a balnk cell between tasks (text)
If the value of the adjacent cell in column A is blank, insert a blank row through Column B:J.
View 9 Replies View RelatedI have in column D 1200 rows of data. I need to insert a blank cell after every 10 rows. Is there a formula or code to do this?
View 9 Replies View RelatedI have a spreadsheet with the following (example) :
Column A :
alabama
alabama
alabama
arizona
arizona
arizona
arkansas
arkansas
I want to insert a blank line (row) wherever the state name changes.....
I am trying to write a macro which will insert a blank row at the end of each year(A column) (Last cell i.e Dec 95 and so on).
I have attached the file for the same ....
I was wondering if someone might be able to assist me with using VB to insert a new row below a cell containing specific text.
For example:
- All of my data is in column A
-I want to scan all of column A, and if there is a cell that contains "ACHCAMERIGROUP M", then I want a blank row inserted below it. If column A does NOT contain that text....do nothing.
I need to put an extra "spacer" cell in each row that contains either 8 or 9 blank spacer cells in columns A through H or A through I, respectively, so that everything in those rows shifts to the right by one column. The problem is that all the other rows don't need any extra spacer cells. There is no pattern to the rows that need the extra spacer cell (such as every 5th row, or something).
View 3 Replies View RelatedI was trying to use a modified version of JBeaucaire's code to achieve the same results within my form. In my workbook I have a a table (called Table27) that ranges from A7:CL109
This is the code I'm attempting to use:
[Code] .....
Column A is where my target cells are... where the user will enter data. In cell CI (the 87th column) I want the Data & Time stamped.
I thought I'd modified the formula correctly, but I can't seem to get it to work.
May seem straightforward, but not to me. I need some code to scroll down column A and insert the sum total of A2:A16 in B17, and repeat this everytime a blank cell appears in colum B, continuing down to the end of the worksheet.
View 5 Replies View RelatedI need to do two things in my Excel spreadsheet: 1) I want it to insert a blank row everytime a value in the 1st column changes. 2) In the blank lines, I want to do a COUNTA for each of columns G through N. If I can get the program to insert the blank rows...they will not be a set # of records apart...some will have 3 records & some might have 17 records. Is there a way to automatically cause #2 to happen instead of having to choose the function icon and then tell Excel the first and last cells in each range?
View 1 Replies View RelatedIs there a way to insert a blank row between every row with data? This is for very large spreadsheets when adding blank rows individually is not an option.
Example:
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Needs to look like this:
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
Data Data Data Data
I have a range of numbers in a single column and I want to insert a blank cell or line below each cell in the range. Is there a quick way to do this, by not using VBA.
View 3 Replies View RelatedI have data like this in column A
ram
ram
ram
reddy
reddy
reddy
lakshman
lakshman
lakshman
and i want to incert blank after the end of each group and i am expecting like this
ram
ram
ram
reddy
reddy
reddy
lakshman
lakshman
lakshman
is it possible to do with simple formulas or i need macro for this? i have huge file with lot of groups like this i can not do this manually by using filters. have look on attched sheet for better understanding.
sample (2).xlsx‎
I am having an excel file in which there is information about 100 products. Every product has around 10-15 features. This way I have 1,000 rows. Every product has its own unique ID i.e. every 10 rows have the same ID thereby making 100 ID's. Now I want to insert a blank row every time the product ID changes so that I can discriminate better between the products. Is there some formula?
A
A
A
A
A
B
B
B
B
C
C
C
C
I want to insert blank rows before B and C. If the number of features were constant, it would have been easy for me.
I have table as show below and i need to insert a blank row after every row and insert a constant data on clolumn B and C and formula on Coloums D, E , F , G ,H and I
Constant data on B : INS Acc
Constant Data on C : INS Desc
Formula:
DX= D(X-1) X- Cell number
EX= F(X-1)
FX= E(X-1)
GX= H(X-1)
HX= G(X-1)
[Code] ........
Expected Output:
Line
Acc
Desc
Cur
ED
EC
AC
AD
Date
[Code] ........
I have an expenses sheet where I would like to add something I saw on a template...
press "tab" in the last populated cell of a table to insert a blank row. (below or above, it doesn't matter) So I can add the next expense.
i want to insert page break after blank row,
john
mohan
kedar
--------------
pagebreak
john
mohan
kedar
---------
pagebreak
After importing a data set into the worksheet entitled "SPACE", I am trying to find the column entitled "Variability Cpu" and then for every cell in that column that has a value in it, I would like to insert a new row beneath that cell. I thought my code made sense, but I get the "Run-time error '424': Object required" error on the line in red below so apparently I'm missing something. why I'm getting this error (doesn't the object variable shtS refer to the worksheet object "SPACE"? Why is it saying object required?). Here's what I've got so far:
[Code] .....
I have a set where there are multiple rows on same date ( ascending order). The data set looks like following:
Date Variable 1 Variable 2 Variable 3
1/01/2014 (M-D-Y)
1/01/2014
1/01/2014
Insert a new row over here and calculate auto sum from 4 Column (D) onwards for all the data for 1/01/2014
1/02/2014
1/02/2014
1/02/2014
Insert a new row over here and calculate auto sum from 4 Column (D) onwards for all the data for 1/02/2014
As i have 365 days in a year and i have to insert a blank row for every date then calculate the auto sum for all the dates.
i attach my sample excel file here, book1.xls. sheet1, "input". it check the column "C" row by row and if it detect the different content data in next column, it will auto insert a new row. it will ignore the space. below is my coding:
View 3 Replies View RelatedNeed a VBA macro that is similar to the subtotals function but without the subtotals? Basically I want the macro to look at column A and say, for each change in column A insert a blank row… I don’t need to worry about the sorting, I just would like to know how I can tell a spreadsheet to look at a column find changes in that column and just insert a blank row. On the second page of the attached spreadsheet is an example of the desired output… I will probably use this for many different spreadsheets so the simpler and more versatile the better, ie I can change the column I want the macro to look at.
View 3 Replies View RelatedI have the following macro that inserts a blank line when a field changes. This works fine. However I would like to use this in another spreadsheet that has several addition columns of unrelated data. Therefore I want to insert 1 (row of) blank cells only in the columns specified in the range. The range is 3 columns wide, E - G. I think I'm close, but ...
Dim myRow As Long
myRow = 3 'data starting row
Do Until Cells(myRow, 5) = "" ' the # is the column that changes which I want to trigger a blank insertion
If Cells(myRow, 5) = Cells(myRow - 1, 5) Then
myRow = myRow + 1
[Code] ..........
I have a spreadsheet that I need to insert a blank row every other row and then merge that blank row. I can do this with the macro below. My question is that I only want the merge on each blank row to merge A:H. Example would be insert row 9 and merge A9:H9. Insert row 11 and merge A11:H11. and so on till the end.
Code:
Sub insertrow()
Application.ScreenUpdating = False
Rows("9").Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.EntireRow.Insert
ActiveCell.EntireRow.Merge
ActiveCell.Offset(2, 0).Select
Loop
Application.ScreenUpdating = True
End Sub
but this time, the numers of duplicates is variable:
before:
---A
1-6
2-6
3-6
4-1
5-1
6-9
7-9
8-9
9-9
ETC.
and after I want to:
---A
1--6
2--6........
I need to insert blank row/s between the missing dates. Let me explain in detail,
I have a start date in Cell B1 and end date in Cell D1. Need a macro to verify the date range in Col B with these start & end dates and insert blank row for missing date corresponding to employee names in Col A. This should be repeat till last used row of the sheet.
Attached sample (Before&After) workbook.
Insert Missing Date.xlsx‎
I have this code which adds a blank row above the name "EV01_" where it appears. ...which works fine, but only works for half the rows.
[Code] ....
I am having trouble trying to figure out how to insert blank cells:
I need to insert cells, in columns B through H, equal to the number of returned IDs in column B.
Please take a look at the spreadsheet I attached.
Here is the code I am using to separate the IDs in column B so that I get one ID in each A cell. When I do this it separates them great, but then I have to manually go in and insert cells down so that the rest of the rows match up with the right ID.
Sub Macro1()
Dim fromCol As String
Dim toCol As String
Dim fromRow As String
Dim toRow As String
[Code] .....
sample of separate.xlsm
I am trying to come up with a code that will copy column A entirely, and insert the copied cells into the first blank column. In the columns with information in them, there will always be text in the first cell, so that can be used as the test to find the first blank column, but I'm not sure how to get this done for my macro.
View 12 Replies View RelatedI would like to be able to add vb code to my developing macro that searches within 1 column for the last instance of a particular word, then inserts a blank row under that word. All the data is sorted, so the words will be used multiple times, but I need the row to go under the last instance of each word in the column.
I want my macro to consider the # of rows of data on a spreadsheet and insert values into columns for those rows but then stop when it hits a blank one. For simplified example below, I have 3 columns in my spreadsheet. Row 1 has headers of Location, Status, and Effective Date. The first 5 rows of data under the headers have a value in the Location column (San Jose, Oakland, etc). When I run the macro, I want it to insert a value of "ACTIVE" in the Status column for each row that has a value in the Location column. Also, a single Effective Date value is stored in a cell elsewere in the spreadsheet, and I want the macro to insert that value in the Effective Date column for each row that has a value in the Location column. When it hits a blank row - row 7 in this spreadsheet - I want it to stop.
Location| Status | Effective Date
San Jose
Oakland
Austin
Houston
Phoenix
I currently am using an IF statement in the Status column fields to say if Location = blank, then blank, else "ACTIVE". That works, except that the # of rows populated in Location column could be 5 or 1,000, and inserting the formula that many times seems to quickly increase the file size.
