VBA - Copy/Pasted range to shift up if there are empty cells in b/w

fullspeed

New Member
Joined
Sep 8, 2014
Messages
6
Hi, I just started learning VBA by watching Youtube videos and reading books. I can't figure out what the issues are with the below codes. what I am trying to accomplish is
1) Copy/Paste range of cells from sheet1 tab to a tab called "Output"

2) Once the data is copied, look through empty cells and shift up so there are no longer empty cells

But I am getting this error: " Compile error: Type Mismatch "
Maybe the order of the codes are wrong? do I need to declare variables before copy/paste? Also, I am not sure what the rules are for having separate sub routines?
Thank you very much for your help in advance!




Sub CopyPasteRange()


Worksheets("Output").Range("A1:C5").ClearContents
Worksheets("Sheet1").Range("A1:C5").Copy
Worksheets("Output").Range("A1:C5").PasteSpecial


End Sub


Sub ShiftValuesToTop()


Dim rei As String
Dim OPsheet As Worksheet
Set OPsheet = ThisWorkbook.Sheets("Output")


For rei = 1 To 3 'colums A to C
OPsheet.Range(Cells(1, rei), Cells(5, rei)). _
SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
Next rei


MsgBox rei.Cells.Count


End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
What line is highlighted when you get the compile error? The msgbox line will generate a run time error b/c rei is an integer having no cells property.
 
Upvote 0
Hi Joe,
The line that's highlighted is this: Sub ShiftValuesToTop()

Thank you


 
Last edited:
Upvote 0
Hi Joe,
The line that's highlighted is this: Sub ShiftValuesToTop()

Thank you


I don't think that's the line that gets highlighted. It's probably this line:

For rei = 1 To 3 'colums A to C

because you have dimensioned rei as a string. Try dimensioning rei as a Long and get rid of the msgbox so that doesn't cause a run time error.
 
Upvote 0
I don't think that's the line that gets highlighted. It's probably this line:

For rei = 1 To 3 'colums A to C

because you have dimensioned rei as a string. Try dimensioning rei as a Long and get rid of the msgbox so that doesn't cause a run time error.

Hi Joe,
I may be missing something completely.
the cells have texts and numbers. that's why I chose string instead of long.

I changed it to Long now, and run
CopyPasteRange - the first sub, it runs.
then when I run
ShiftValuesToTop, it says
run-time error '1004':
No cells were found.

when i click on debug, it highlights below part in yellow:
OPsheet.Range(Cells(1, rei), Cells(5, rei)). _
SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
 
Upvote 0
Hi Joe,
I may be missing something completely.
the cells have texts and numbers. that's why I chose string instead of long.

I changed it to Long now, and run
CopyPasteRange - the first sub, it runs.
then when I run
ShiftValuesToTop, it says
run-time error '1004':
No cells were found.

when i click on debug, it highlights below part in yellow:
OPsheet.Range(Cells(1, rei), Cells(5, rei)). _
SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp

The specialcells method generates an error if no specialcells meeting the criteria (in your case that's blank cells) are found in the designated range. You can avoid the error message by preceding the specialcells line with "On Error resume Next", but that doesn't eliminate the error just the message and code disruption.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,214,367
Messages
6,119,114
Members
448,870
Latest member
max_pedreira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top