NoSparks



Column "A
1 aaaa
2 blank
3 bbbb
4 blank
5 comma
7 cccc
6 blank
8 comma
9 dddd
10 eeee

Column "B"
1 aaaa
2 bbbb
3 cccc
4 dddd
5 eeee

I receive data in a vertical configuration (column "A"). However the data shows up in an inconsistent manner because the number of blank spaces separating the desired data varies. In addition, there are commas randomly included in the column "A". What I am wanting to do is to transfer the data in column "A" to column "B" so that each category of data (aaaa, bbbb, cccc, dddd,eeee) will reside in a constant address. What you have provided me so far does the job,.

However, I mistakenly specified to delete the "entire row" where blanks occurred instead of just deleting the "cell "where the blank occurred. I also neglected to mention where the commas occur that I want to those cells to be deleted as well.

In addition to the info you have provided,
Range("A1", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlBlanks).EntireRow.D elete

if I just wanted to delete the blank cells, to would I change "EntireRow.Delete" ? And to what would I change (xlBlanks) to get rid of the commas?

You want to delete the cell but not the row ???
Okay, so what's to happen?
Cells from below move up or cells from the right move left?

Seeing column B is now in the picture, maybe this is what you need

Cells from below move up.
I will try your suggested procedure. All the same, I am curious regarding my question about changing "EntireRow.Delete" to "deleting the cell" and changing (xlBlanks) to what to get rid of the commas? Thanks once again.

Your solution works exactly as to what I need. I actually start at "A33" instead of "A1". I adjusted your code to reflect that. I would ask you to humor an old man and make a slight mod to the procedure. Could you extend the code to have the data in column "B" to overwrite the data in column "A" (starting at row 33)....keeping in mind that the number of rows in column A is greater than the number of rows in col B. In addition, would you make the data right justified in each cell. Presently the data received is a mixture of justification

Hey..... stop with that old man stuff, I'm an Old Age Pensioner too ya know.

Except for the 33 and HorizontalAlignment this is what I had prior to seeing your last post.

xlBlanks is an built in constant.
In the vba environment, place your cursor within SpecialCells and hit F1 to find out things like this.

You could remove the commas to make those cells blank, then delete.

Senior citizen NoSparks,
Thank you for all your help on my project. This last assistance you provided me has gotten me to the final product. I am now ready to implement the program with which you have so generously helped me. Hopefully I will not encounter and glitches, but if I do I will be back. Thanking you and Jeffrey Brown for getting me to this point.

NoSparks,

Just when I thought I was ready to rock and roll, I encountered one more thing I need to make my program ready.
I have a spreadsheet (spreadsheet 1) that consist of multiple rows from which I will select one row and copy the data in that row to another spreadsheet (spreadsheet 2) for modification. I want to maintain the original data in spreadsheet 1 as it exist. After modifying that row of data within spreadsheet 2, I want to insert that modified row of data from spreadsheet 2 back into spreadsheet 1, above the original row that I copied from.
Can you help me with this issue?
Thanks in advance for any help regarding this

All questions this time...

How are you determining what original data row to copy?
Where do you put it on sheet2?
How do you manipulate the data? Manually or existing macro?
How do you insert the manipulated data back into sheet1?
Why are you doing all this?

Have you used the macro recorder to get an idea of how this would go in a macro?

How are you determining what original data row to copy?
I have large list of items that I downloaded from the internet that need to be modified. However I need to maintain a copy of the original.

Where do you put it on sheet2?
The data from sheet 1 is loaded into a form like configuration on sheet 2 for modification.

How do you manipulate the data? Manually or existing macro?
I use macros that I have made by recording and some which you and Jeffrey Brown have assisted me.

How do you insert the manipulated data back into sheet1?
Sheets("sheet 2").Range("A3:Z3").Copy
Sheets("sheet 1").Range("A7").Rows("1:1").Insert Shift:=xlDown

On a small spreadsheet this works fine, however it gets quite awkward with a large spreadsheet. I can function as it is but it would be more efficient and less cumbersome if I can reinsert the modified version above the original.

Why are you doing all this?
Helping someone...gratis

Have you used the macro recorder to get an idea of how this would go in a macro?
The problem with macro recorder is that it does not take into account that each time it will be a different row that must be inserted and pasted into.

I understand if you are a bit tired of contributing to my effort. Regardless, you have made significant contribution to my project and I appreciate all that you have offered.

The modified copies are variations of the original. There will be several variations of each original.

In addition to recording macros and assistance from this forum, I modify different things I find on the internet. That approach is trial and error. Mostly error. It has resulted in some successes. However, my latest issue has resulted in error so far.

Hmmm... a couple of those questions didn't get interpreted the way I had hoped.

1.) How are you determining what original data row to copy?
In order to automate everything you need to know where to start, what row?
What is it that determines this? How can this be logically used in a macro?

2.) Why are you doing all this?
It's great that you're a nice guy, but what I was trying to get at was why do you take things from one sheet to another and back again?

an example as to why I ask... the move back and forth could be replaced by this if you knew what the row is and if it could be manipulated in place.

I click a cell in sheet 1 and run a macro that sends it to my sheet 2. This works fine. I determine which cell based upon the fact that it is an original and I need to add variations of that cell which will be done in sheet 2. This works fine.

I take things from one sheet (spreadsheet 1 containing all the rows of data) to a form sheet set up as a data entry form. Easier to look at one record in a form configuration than a spreadsheet that has columns a thru z and a boatload of rows. This works fine.

The whole program works good as it stands... but as it stands I return data to sheet 1 address "A7" thru "Z7" every time from sheet 2 row "A3" thru "Z3 ". What I want to do is copy data from sheet 2 (row "A3" thru "Z3 ") to an inserted row above the original copied row on sheet 1....and have the inserted row be the active row.

If I am able to accomplish what I want, the only constant will be the address of the copied data from sheet 2 (sheet 2 row "A3" thru "Z3 ") The row to paste that data on sheet 1 will always be a variable determined by the row I select for modification on sheet 2.

I suspect you get the original row to copy by referencing the active cell and want to do the same to copy back to sheet1.
You're needing to test things to see what happens.

Open a new workbook
Make A1 to A5, 1 thru 5
Select A3 to make it the active cell
Alt+F11 to bring up the VB environment
Ctrl+g to bring up the Immediate window
type in
rows(3).insert shift:=xldown
hit Enter..... is A3 not still the active cell and on the inserted row ?

The active cell can be used to determine the copy, insert and paste.

yes "A3" is the active cell and on the inserted row.