Macro for empty cell

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

farmerscott

Well-known Member
Joined
Jan 26, 2013
Messages
813
Office Version
  1. 365
Platform
  1. Windows
swarren,

Correct. Cells(x,1).value="" clears the cell or it has no value.

Watch that you do not put Cells(x,1).value=" " (note the extra space). That puts a space in the cell and can cause further problems.

Or are you asking to test if the cell is empty? Use something like the function LEN(A1).

Hope that helps,

FarmerScott
 

swarren

New Member
Joined
Jul 16, 2014
Messages
9
Thanks FarmerScott,
I am new to vb. I have 4 spreadsheet n excel. I copy and paste from the first one to the others and I recorded a macro. How do I tell it to stop at a blank cell the continue to the end? Here is my macro:
Range("B2:B8,D2:I8").Select
Range("D2").Activate
Selection.Copy
Sheets("Document").Select
Range("D2").Select
ActiveSheet.Paste
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Range("A3:A8").Select
ActiveSheet.Paste
Range("A2:A8").Select
Application.CutCopyMode = False
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Input").Select
Range("D2:E8,J2:M8").Select
Range("J2").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Transaction").Select
Range("D2").Select
ActiveSheet.Paste
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Range("A3:A8").Select
ActiveSheet.Paste
Range("A2:A8").Select
Application.CutCopyMode = False
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Document").Select
Range("B2:J8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("UploadAuto").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Transaction").Select
Range("B2:C8,F2:I8").Select
Range("F2").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("UploadAuto").Select
Range("A8").Select
ActiveSheet.Paste
 

farmerscott

Well-known Member
Joined
Jan 26, 2013
Messages
813
Office Version
  1. 365
Platform
  1. Windows
I am unsure how your comment, "How do I tell it to stop at a blank cell the continue to the end?", fits into your code.

Your code seems to do a lot of copying and pasting to other sheets. What are the names of all your sheets including the 'master' sheet.

Can you give a description of what you want the code to do outlining each step and where the data resides.
A small copy of your data would be helpful.

cheers

Farmerscott
 

swarren

New Member
Joined
Jul 16, 2014
Messages
9
1st sheet=input, 2nd =document 3rd transaction & upload. I have a transaction sheets I get daily from different companies each varies with amount if data. I am trying to get this information combined on to the last spreadsheet (input) to upload into my acct. system. The doc sheet is one level of my entry screen and the trans sheet is another level, then I combine the 2 sheets to my input sheet and upload. I hope this explains it better because I don't know how to attach copies of the worksheets to this. Thanks for your time.
 

farmerscott

Well-known Member
Joined
Jan 26, 2013
Messages
813
Office Version
  1. 365
Platform
  1. Windows
swarren,

Sorry I have been busy and not getting back to you on this....

So if I have this correct you want to combine the information on the sheet "Document" and sheet "Transactions" onto sheet "Input"?

I need to get a series of precise steps that you want the code to do..for example

1. Copy from sheet (??) in the ranges "B2:B8" and "D2:I8" to sheet (??) in the range of (??)
2. When pasting over to sheet(??), it will have data in columns (??). The column with the longest data is ??
3.
4. etc

If you can click on "Go Advanced" down the bottom right of your screen. Use the table function to build a copy of your data you want copied and another of what it will look like (on the pasted sheet).

For example-

With sheet1, I want to copy range "A1" and range "B2:B3"
Col ACol B
112345
254321
39876

<TBODY>
</TBODY>

I then want to copy to sheet 2 underneath existing data

Col ACol B
13456
25678
36789
412345
554321
69876
7

<TBODY>
</TBODY>


This will make writing the code much easier.

thanks

FarmerScott
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,470
Members
409,884
Latest member
Msinmath
Top