?
Solved

VBA Syntax Excel 2007

Posted on 2011-04-27
3
Medium Priority
?
463 Views
Last Modified: 2012-05-11
I want to delete a few cells in a sheet if they are blank and then 'Shift Cells up' option.

I tried this (see below)  but it does not work can anyone tell me how to reference the 3 cells I'm deleting? It stops on this line: " Range("CellFr: Cellto").Select"
Error: "Method range of oblect global failed"

Dim CellFr
Dim CellTo

    Sheets("Sheet1").Select
    Range("A8").Select
   
    Do While ActiveCell.Text > ""
   
CellFr = ActiveCell.Offset(-1, 0).Address
CellTo = ActiveCell.Offset(-1, 2).Address
    If ActiveCell.Offset(-1, 1) + ActiveCell.Offset(-1, 2) > 0 Then
    GoTo Lab1
    Else
        Range("CellFr: Cellto").Select
       
    Selection.Delete Shift:=xlUp
    End If
   
Lab1:
     ActiveCell.Offset(1, 0).Select

    Loop
0
Comment
Question by:HKFuey
  • 2
3 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35474857
You can use:
 Range(CellFr & ":" & Cellto).Delete Shift:=xlUp

Open in new window


HTH
Rory
0
 

Author Comment

by:HKFuey
ID: 35475239
rorra, thanks very much!. I ended up using: -

Dim CellFr
Dim CellTo

    Sheets("Sheet1").Select
    Range("A8").Select
   
    Do While ActiveCell.Text > ""
   
CellFr = ActiveCell.Offset(-1, 0).Address
CellTo = ActiveCell.Offset(-1, 2).Address
    If ActiveCell.Offset(-1, 1) + ActiveCell.Offset(-1, 2) > 0 Then
    GoTo Lab1
    Else
    Range(CellFr & ":" & CellTo).Select
    Selection.Delete Shift:=xlUp
   
    End If
Lab1:
     ActiveCell.Offset(1, 0).Select
    Loop
0
 

Author Closing Comment

by:HKFuey
ID: 35475249
Thanks!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question