Need to check is cells are empty before pasting

**karlosdejackal** · 02-05-2015, 03:14 PM

Hi,

I have written a simple copy&paste macro.

I was wondering if anyone had any suggestions for how to check if there is no data on the row before pasting? (Seeing as there is no undo)

I guess I need and If IsEmpty type function and Else then give an error.

Sub Update_Month()

' Update the fills for the specific day

Dim number As Integer
Dim row_number As Integer

'enter number in day box at B8, then this will paste to the correct place on sheet

row_number = Sheets("Data").Range("B8").Value + 3

'I need an if statement for here I think that looks at the row entered (row_number) and checks that it is empty or zero from column 2 to 500

Sheets("Data").Select
Range("A5:HD5").Select
Selection.Copy
Sheets("Current").Select
Cells(row_number, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Else an error message

End Sub

Could anyone help me on this? Particularly in using my row_number to look across a range.

Thanks

**stnkynts** · 02-05-2015, 03:19 PM

Do you mean like this?

If Len(Sheets("Data").Range("B8").Value) = 0 Then
    'empty value
    row_number = 2 'or anything you want to do in this situation
Else
    row_number = Sheets("Data").Range("B8").Value + 3
End If

**karlosdejackal** · 02-05-2015, 04:46 PM

Hi,

Thank you for your offer of help, but this is not what I am really looking for.

For example if value 12 is put in the day box (Sheets("Data").Range("B8").Value) , then data gets pasted to Sheets("Current").Cells(row_number, 2)

I need the macro to look across row_number and from columns B - GG. To see if these are empty

The issue is I don't know how to define this range using row_number and get it to check that there is no data before pasting

In this example it would be Range (15B:15GG). But I need the row to change with row_number.

Thanks

**stnkynts** · 02-05-2015, 05:13 PM

This is still clear as mud for me. Maybe this will explain things for you

Sub Example()
Dim row_number As Long, num_blanks As Long
Dim rng As Range

'in this situation Range B8 is 12
row_number = Sheets("Data").Range("B8").Value + 3

'this will resize your range to encorporate columns B to GG for that given row_number
Set rng = Sheets("Current").Cells(row_number, 2).Resize(1, 188)

'proof it works
MsgBox rng.Address


'to see if there is at least 1 empty cell in rng
num_blanks = Application.WorksheetFunction.CountBlank(rng)

'proof it works
If Application.WorksheetFunction.CountBlank(rng) > 0 Then
    MsgBox ("There are " & num_blanks & " blank cells in Range" & rng.Address)
Else
    MsgBox ("There are no blanks in Range" & rng.Address)
End If
 
End Sub

Need to check is cells are empty before pasting

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Need to check is cells are empty before pasting

Re: Need to check is cells are empty before pasting

Re: Need to check is cells are empty before pasting

Re: Need to check is cells are empty before pasting

Thread Information

Users Browsing this Thread

Similar Threads

Need to check if cells empty before pasting

Empty cells not evaluating as empty - multiple check methods tried

pasting values into EMPTY cells

Check for empty cells but ignoring value ZERO

check for non-empty cells outside of a certain range

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions