Link to home
Create AccountLog in
Avatar of Debra45
Debra45Flag for United States of America

asked on

Need Excel VBA macro to copy/paste without blank rows

I need to create an Excel VBA macro that can copy a block of cells from an Excel spreadsheet while eliminating the blank rows that are present.  Please see the worksheet "Clinical Notes - Consent" in the enclosed spreadsheet
Sample-sheet.xlsm
Avatar of SiddharthRout
SiddharthRout
Flag of India image

You don't need to use a copy and paste option...

In fact this way you can store the data in a variable by running the below macro...

Sub Sample()
    Dim cl As Range
    Dim strtemp As String
    
    For Each cl In Selection
        If Len(Trim(strtemp)) = 0 Then
            strtemp = cl.Value
        ElseIf Len(Trim(cl.Value)) <> 0 Then
            strtemp = strtemp & vbCrLf & cl.Value
        End If
    Next
    
    MsgBox strtemp
End Sub

Open in new window


To test this, click on the link in cell G5 in "Clinical Notes - Consent" and then run the macro. You may amend the code to directly store the value of strTemp in the destination cell.

Sid
The above is just an example.

We can similarly loop through all rows and get the data so that the data is displayed as it is present in the source cells... Let me create a more apt example for you based on your worksheet.

Sid
SOLUTION
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Debra45

ASKER

When I paste your macro into my spreadsheet, I get the error message enclosed.
Error---Screen-1.JPG
Error---Screen-2.JPG
Did you select the entire range before running the macro?

Sid
Avatar of Debra45

ASKER

Yes
I just tested it and it works just fine...

What is the value of 'i' at the time of error?

Sid
Ok try the sample that I have posted which is your file...

Click on the link so that the entire range is selected and click on macros ~~> Sample

Does it copy the range to sheet "Sample", Cell A1?

Sid
Avatar of Debra45

ASKER

It works on the sample you sent earlier and copies to Sample cell A1.  I only get the error message when I copy the macro into my larger spreadsheet.   Please clarify your question "What is the value of "i" at the time of the error."  I do not understand your question.
When you get that yellow line on error, simply hover the mouse over Rows(i) and see what is the value of i.

Also Can I see the actual file so that I can test it for you?

Sid
When you get that yellow line on error, simply hover the mouse over Rows(i) and see what is the value of i.

Also Can I see the actual file so that I can test it for you?

Sid
When you get that yellow line on error, simply hover the mouse over Rows(i) and see what is the value of i.

Also Can I see the actual file so that I can test it for you?

Sid
Avatar of Debra45

ASKER

The value of "i" is 6
The spreadsheet is a copyrighted work with all of the formulas protected.  Is there a way I can send the file to you where it would not be posted on Experts Exchange?
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Debra45

ASKER

That works.  Two questions:
1) How do I lock the macros where someone cannot look up the password
2) Is there a way to add to the macro so that the data copied to the "Sample" sheet is preselected, ready to paste?

By the way, I see you are available to hire for programming.  Have you done any work adapting spreadsheets for cloud computing via PHP?

Thanks
Avatar of Debra45

ASKER

I've also tried applying this to another sheet in the workbook.  That sheet has a LOT of formatted numbers that must be copied over.  Is there a way to preserve the formatting?  For example, cells that are formatted to display time (7:00 AM) display 0.33333.
1) How do I lock the macros where someone cannot look up the password

Click on VBA Menu~~>Tools~~>VBAProject Properties.

Under the "Protection Tab", click on the checkbox "Lock Project for viewing" and enter the passwords.

2) Is there a way to add to the macro so that the data copied to the "Sample" sheet is preselected, ready to paste?

Yes, simply pass these lines of code

'~~> Replace "SheetName" with the relevant Sheetname and also set the relevant range.
Sheets("SheetName").Activate
Range("A1:B10").Select

Open in new window


Have you done any work adapting spreadsheets for cloud computing via PHP?

Nope. Dunno php.

Sid
I've also tried applying this to another sheet in the workbook.  That sheet has a LOT of formatted numbers that must be copied over.  Is there a way to preserve the formatting?  For example, cells that are formatted to display time (7:00 AM) display 0.33333.

The easiest way that I can think of is to copy the entire range to a temp worksheet and then delete the blank rows and then copy it back to the relevant area...

Sid
Also you need to close and reopen the workbook to see the "Locking" effect...

Sid
Avatar of Debra45

ASKER

Question 1:
I don't see how these lines below can work if the amount of data selected is variable:
 '~~> Replace "SheetName" with the relevant Sheetname and also set the relevant range.
Sheets("SheetName").Activate
Range("A1:B10").Select

Question 2:
Likewise, the idea below isn't practical.  The area that is originally formatted has a bunhc of lines that we've just gotten rid of.  If we paste back into that area, the cells do not line up correctly
"The easiest way that I can think of is to copy the entire range to a temp worksheet and then delete the blank rows and then copy it back to the relevant area..."

Question 3:
Sorry, I missed why you made the reference for the  need to close/reopen for the "locking effect."

Thanks for your patience!
Question 3:
Sorry, I missed why you made the reference for the  need to close/reopen for the "locking effect."

1) How do I lock the macros where someone cannot look up the password


Click on VBA Menu~~>Tools~~>VBAProject Properties.

Under the "Protection Tab", click on the checkbox "Lock Project for viewing" and enter the passwords.
Question 1:
I don't see how these lines below can work if the amount of data selected is variable:
 '~~> Replace "SheetName" with the relevant Sheetname and also set the relevant range.
Sheets("SheetName").Activate
Range("A1:B10").Select

I thought you wanted to select a Range in the code...

Could you please explain what you meant by "amount of data selected is variable"

Sid
Avatar of Debra45

ASKER

Could you please explain what you meant by "amount of data selected is variable":
When the data is selected and the macro is run, the blank lines are eliminated.  Therefore the area that needs to be selected for pasting will always be a different size.  I can't just set the macro to select a specific range.  Does that make sense?
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
Avatar of Debra45

ASKER

I agree with your solution.  This was only my second use of Experts Exchange, and I failed to realize that it was improper to change directions in the middle of the question.  I will repost in a few days to finish the minor issues.  Thanks for the assistance!.