I have data in cells a1 b1 c2 d2 a5 b5 c9 d9 as an example. If you load this into a spreadsheet and look at it, you wouldn't want to print it out looking like that. I have that problem. I need to send a spreadsheet like this to people, and I have over 100 spreadsheets to send. So, I don't want to delete the blank cells manually because they contain formulas, and it would take me many hours to do it. What I'm looking for is, a function or formula set that will look at cell a1 and if it contains any text to copy it into cell g1. Then look at the new spreadsheet without any hidden formulas in it and then delete the blank cells so it looks acceptable to email to someone. Thanks to everyone for your help with this.
Checking data and copying to cell
- Thread starter union865
- Start date
- Joined
- Dec 16, 2012
- Messages
- 2,327
- Reaction score
- 40
- Points
- 48
- Excel Version(s)
- 365
So how would you want it looking!?
OK. So that deals with cell A1, what about the rest?
The following macro will copy values from a block (as defined in the first line) in the active sheet to a new sheet as values. On that new sheet, it will remove blanks.
I can only guess what you want so it's probably wrong. (I notice that the ranges you quote are in pairs, so maybe you want the results in just two columns? I really don't know as I can neither look over your shoulder to see what's on your screen nor read your mind.)
Code:
Sub Macro7()
SourceRangeAddress = "A1:D9" '<< adjust this.
Set oldSht = ActiveSheet
Set NewSht = Sheets.Add
NewSht.Range("A1").Resize(Range(SourceRangeAddress).Rows.Count, Range(SourceRangeAddress).Columns.Count).Value = oldSht.Range(SourceRangeAddress).Value
NewSht.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End Sub