Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignoring a blank cell while running a macro.
Dear All
I have the following macro attached to a button to copy data F10:G40 (This months figures) to next free columns, so I can build up a yrs worth of data. I then repopulate F10:G40 with the new months data, then at the end of the month click the button and start over. The problem is column "H" which is blank. I want the macro to start pasting from column " I ". Is there a macro txt string that will ignor a blank cell. I don't want to nominate a particular cell, say =IF(C:C) because when I transfer this to another sheet, the refs won't be the same. I just need to generically skip any blanks. Thanks in advance. P.S I am new to macros so don't get all your terminology, sorry but it needs to be idiot proof !!! Range("F10:G40").Select Selection.Copy ActiveWindow.SmallScroll Down:=-30 (Question ? what does this mean & can I take it out ?) ActiveCell.End(xlToRight).Select ActiveCell.Offset(0, 1).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ignoring a blank cell while running a macro.
On Oct 11, 4:09*am, Decreenisi wrote:
Dear All I have the following macro attached to a button to copy data F10:G40 (This months figures) to next free columns, so I can build up a yrs worth of data. I then repopulate F10:G40 with the new months data, then at the end of the month click the button and start over. The problem is column "H" which is blank. I want the macro to start pasting from column " I ". Is there a macro txt string that will ignor a blank cell. I don't want to nominate a particular cell, say =IF(C:C) because when I transfer this to another sheet, the refs won't be the same. I just need to generically skip any blanks. Thanks in advance. P.S I am new to macros so don't get all your terminology, sorry but it needs to be idiot proof !!! Range("F10:G40").Select Selection.Copy ActiveWindow.SmallScroll Down:=-30 (Question ? what does this mean & can I take it out ?) ActiveCell.End(xlToRight).Select ActiveCell.Offset(0, 1).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Range("A1").Select End Sub If I understand what you want, try just this. But wouldn't it be better to copy below? Sub copyrange() Dim dlc As Long dlc = Cells(10, Columns.Count).End(xlToLeft).Offset(, 2).Column Range("F10:G40").Copy With Cells(10, dlc) .PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .PasteSpecial Paste:=xlPasteColumnWidths End With Application.CutCopyMode = False Range("A1").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if every cell is larger than x, but ignoring blank cells | Excel Worksheet Functions | |||
Select next blank cell in column, before running macro | Excel Programming | |||
Want to check if cell is blank before running macro | Excel Programming | |||
Highlight Lowest value ignoring 0 or blank cell | Excel Discussion (Misc queries) | |||
showing a cell when it is not blank and ignoring it if it is | Excel Discussion (Misc queries) |