Add Character To 1st Blank Cell In Multiple Rows

Jun 28, 2008

I am looking for VBA code that will add the character ")" to the first blank cell in each row. The amount of columns in each row changes and the amount of rows change. I attached a sample spreadsheet.

View 2 Replies


Hide Multiple Rows Based On Cell Value Being Blank

Feb 20, 2013

I have a worksheet that contains 15 instances of a repeated table over 700 rows. Each table is 45 rows in size and is housed in between the natural page breaks in the spreadsheet.

These tables are populated from data form another worksheet but may not all be used (8 out of the 15 may be used but will always start from table 1 and there will be no missed tables).

In the very top right of the table is a cell value that is only displayed if the table is in use, so will be blank if not used.

Code that will hide multiple rows (45) based on a cell value being blank.

View 2 Replies View Related

Combined If Statements - Make The Second Character In The Blank Cell?

Mar 6, 2014

I have a spreadsheet that i want to create a part number that contains 4 parts. Ex 1rl9. The cell i want this to be in is blank. What formula do i use to make the second character in the blank cell = r?

View 9 Replies View Related

IF Statement To Leave Cell Blank If Multiple Cells Are All Blank?

Mar 12, 2014

I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.

View 8 Replies View Related

Insert Multiple Blank Rows At Designated Cells

Jun 13, 2014

I have a large list of cells in excel: 15, 33, 90, 102, 149, 159, 217, 228, 238, 247, 305, 312, 369, 417, 428, 486, 538, 548, 606, 621, 671, 679, 737, 805, 816, 874, 915, 923, 981, 1029,1038 .

Under each of these cells I would like to insert 20 blank rows. I have tried various codes but i'm struggling with the fact that as soon as I insert 20 rows at cell 15, all the other cellnumbers change.

This is a reformulation of this post: [URL] ...........

View 1 Replies View Related

Deleting Rows Where Cells In Multiple Columns Are Blank

Apr 17, 2013

I work with a spreadsheet every week to input values and subtotal them. These values change constantly and instead of going through and manually deleting each row in a 100+ row spreadsheet to be able to import into another program, I'm looking for an quicker way to keep my data in order but consolidate by getting rid of only the rows where both column A and B are blank.

Here's an example of what I'm working with:


This is what I need the final product to look like:


So I would like to quickly delete rows 2, 5, and 11. All of the other answers I've found only show how to delete rows based on empty cells in only 1 column. How can I quickly delete the rows where both columns are empty?

View 5 Replies View Related

Delete First Character If Blank

Oct 21, 2009

My range is A2:T600 on one single worksheet. In many cells, the first character is a blank space (a mistake of mine when creating the initialize code in the user form into which the info was entered). I only need to do this once ever for this worksheet, but I hate to go through them all by hand. So what I need is to select the range, and IF the first character of a cell is a blank space, delete just that character.

View 3 Replies View Related

Convert Multiple Columns To Rows Including Blank Cells

Apr 11, 2014

I currently have a spreadsheet that I had to convert from multiple rows to columns:


Now I need a script to change the data so that each column is now in row format, (see attached spreadsheet).

View 5 Replies View Related

Comparing Values In One Column And Inserting Multiple Blank Rows

Oct 13, 2006

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
' Rows("1:1").Select

View 4 Replies View Related

Enter Blank Row Every Time 2ND Character Of Field Changes

Oct 31, 2011

I am trying to enter a blank row everytime the 2ND character of a field changes...

sample cells (ALWAYS COLUMN B)(last row needs to be calculated as it changes daily):


when done:


was trying this:

Dim chkConfirmRw, LastNameRow As Integer
For chkConfirmRw = LastNameRow To 1 Step -1
'Compare the current cell to the one below it
'If they don't match, insert a row row below the current Row
If Range("B" & chkConfirmRw) Range("B" & chkConfirmRw + 1) Then
Range("B" & chkConfirmRw + 1).EntireRow.Insert Shift:=xlDown
End If
'Decrement the counter and do it again

but it does not work.

View 3 Replies View Related

Look For Character In Cell And Insert New Character - For More Than One

Sep 20, 2009

I have a cell which will contain SER01+SER02+SER03

and what i need it to contain is [SER01]+[SER02]+[SER03]

and shocker is i've got this to work for the first instance but not the other two

code as below... be grateful for your help

Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub

View 9 Replies View Related

Insert Blank Cell After Every 10 Rows

Mar 15, 2013

I 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 Related

Insert A Blank Rows Based On Cell Value

Aug 26, 2009

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 ....

View 13 Replies View Related

Delete ALL Rows That Have Cell In Column (J) That Looks Blank

Feb 11, 2010

I'm trying to delete rows which has columns that appears to be blank. Below code worked fine but it took about 15 min for the macro to work through the spreadsheet.

View 2 Replies View Related

How To Merge Two Rows If Specific Cell Is Blank

Apr 4, 2012

I'm trying to figure out the if conditions for this relatively simple problem. Basically, this is an import from a word document where the table strays onto a new page. I want to try and repair this with a bit of VBA.

This is what it looks like:

1: cell 1 |the content should all be in this cell
2: |but the import sometimes splits it into two
3: cell 2 |

Basically if and only if cell Ax is blank (in this example A2), then I want the rows to merge each cell and repair the table.

View 1 Replies View Related

Hide Rows In Table If Cell Is Blank?

Mar 9, 2014

I'm trying to create a table that adjusts dynamically to how many cells in a row have text in them. In other words, when a cell is left blank, the entire row should not appear in the table.

I've tried the following code:

Sub test()
Dim i As Integer
Dim nrrows As Integer


When I run this code, it hides all rows, even the ones that aren't blank. why this might happen and how I can make it work?

View 9 Replies View Related

Copy Rows Where Column Cell Is Not Blank

Dec 13, 2007

What I have are invoices that span an entire month that I need to convert into a format for the billing department. The columns I have right now are: "Invoice Number"Cost CenterAccountFundType of Service" Date of Completion"Unit PriceQty Total Price

I need to generate a file with the headings as: Cost CenterFund"Date of Completion"Account Total Price. Since Total Price only appears on the rows that have the final information I need, I want to have a script that grabs every line in which the Total Price column has data, generate a file of the summary lines, then throw that through another macro to reformat it into the final format, or just do it in one step. In summary I need to grab rows where Column I is not empty, whats the best way to do this?

View 3 Replies View Related

To Find A Total Number Of Rows To A Blank Cell

Mar 14, 2007

I need to find the total number of rows down to the next blank cell (and then perform a function based on that number).

I'm using:

Situation: I have a raw data import - each record is anywhere from 2 to 9 rows, and I need to move each row in that group into a column.

I would like to use something like:

totalRows = Application.WorksheetFunctions.CountA(Range("A1, xlDown"))
If totalRows = 4 Then
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Offset(-1, 1).Range("A1").Select

View 9 Replies View Related

Excluding Rows Containing A Specific Blank Cell From Logic

May 1, 2006

how to exclude a blanck cell from logic?

I have tried what you see below but neither works as desired.

If Not vData(n, 9) = 0 Then

If Not vData(n, 9)Is null Then

have corrected code error

View 9 Replies View Related

VBA Macro To Filter Rows By Cell Value & Hide Blank Columns

Oct 1, 2008

i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.

basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.

the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.

where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.

View 7 Replies View Related

Count Blank Cells Within A Range Not Including Fully Blank Rows

Jul 15, 2008

I can count the blank cells withiin a range using


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.

View 14 Replies View Related

Excel 2010 :: Multiple Entries In One Cell That Need To Be Spread Across Multiple Rows

Jun 17, 2014

I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.



FILER756911**/**FILER123188 ^** FILER877119*118

[Code] ........




[Code] ......

What the MACRO would look like? This is for Excel 2010.

View 3 Replies View Related

SUMIFS Multiple Criteria Including Blank Cell

Mar 13, 2013

I am trying to create a SUMIFS function to sum estimated hours for specific systems that do not have a Completed date. I do not want to sum remaining hours if it has been completed. i.e.

=SUMIFS(Hours, System#,"110-000-00, Complete (Date), ISBLANK)

System #
Complete (Date)



[Code] .........

View 7 Replies View Related

Separate Multiple Lines In Cell To Multiple Rows?

Jul 10, 2014

I have a challenge where in have a list of about 4000 rows and each cell have mutiple lines. ( i know there is a thread about this but that did not address my issue).

So my data is as follows - (I have attached teh sample file)

"Pari bom - GP and CFO 02547895452

papa bom - GP and CFO 23365789541

mama bom - CFO 2587412589

nana bom - CFO 2587412589

masi bom - CFO 2587412589


I want to be in a position to seperte each line in exclusive rows and then seperate teh name, number and the email ... the later eing easier but cant get my head around seperating the cells.

View 4 Replies View Related

Split Multiple Values In A Cell To Multiple Rows?

Mar 4, 2013

if there is a way to split multiple values in a cell to multiple rows. example check the attached sheet, macro/function with in excel etc.

View 2 Replies View Related

Get Text After Multiple Character

Feb 23, 2013

get the function to get the text in the middle of long text

i have text like this 125/565/797/7222/222/2122

how i put the formula to get text after multiple "/" example the text : 125/565/797/7222/222/2122 in cell A1 i want to get after third char "/ " value 7222 Before the fourth "/"in cell A2 and after the last "/" in cell a3 or value 2122

View 4 Replies View Related

Get The Right Most After A Character Having Multiple Instances

May 28, 2014

What I want to do today is, I want to get the extension (Type) of a file from the string. I have managed to get formula for that.

PHP Code: 


But the problem is, if the name of the file also have any dots, it is not giving correct result.

For Eg, if the string is ABCD_V1.0.mpp, I am getting 0.0.mpp not .mpp

NB: The file name may or may not have dots. So cannot tell that, it is always the second instance. Need to find the last dot and trip the things before that

View 11 Replies View Related

Remove / Replace ONLY FIRST Occurrence Of First Character In All Rows Of Column?

Jul 30, 2013

I need to remove the first character in each row of a column, but only its first occurrence in each row. For instance:

Say, column 2 has following data (lots of rows, by the way):

First Text" - "blabla"
=SomeText2 = "blabla"
SomeText3 = "blabla"
=SomeText4 = "blabla"
-SomeText5 --- "blabla"

I would like to use the VBA code to remove ONLY the FIRST occurrence of the FIRST character specified (either "=" or "-") in each row in that column, so that I get:

First Text" - "blabla"
SomeText2 = "blabla"
SomeText3 = "blabla"
SomeText4 = "blabla"
SomeText5 --- "blabla"
...and so on...

I tried to use this:

Columns(2).Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
Columns(2).Cells.Replace What:="=", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
but it replaces ALL occurrences of "=" and "-" and that is not what I need.

I also tried this code:

With Range("B:B")
.Value = Replace(.Value, "=", "", 1, 1)
End With

But It doesn't work either.

View 7 Replies View Related

One Cell With Multiple Lines Into Multiple Rows?

Jun 24, 2010

I need a macro that will seperate multiple lines in more than one cell into multple rows.

Cells G-N contain multiple lines of data that I must split out into multiple rows; the multiple lines of data must be parced out into the same row.

View 4 Replies View Related

Adding Multiple Cells Text With Special Character

Sep 3, 2013

This is my text:

Test Name1
Test Name2
Test Name3
Test Name4
Test Name5

Required result is:
Test Name1|Test Name2|Test Name3|Test Name4|Test Name5

View 8 Replies View Related

Copyrights 2005-15, All rights reserved