0

I am developing a macro to eliminate blank rows from a worksheet which is used for entering customized orders. Lets say rows 7,8,9 and 12 have contents. I want to move the contents of row 12 to row 10.

So far I've located the last occupied row in column c then identified whether the cell in the row in column e is blank or not.

Now I want to put a value into an array either 0 (blank) or 1 (occupied). I am getting an error (object required) on the line of code that sets the value of stones (1) to 1 or 0.

What is going wrong?

  Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("Belmont")
Set rng1 = ws.Columns("c").Find("*", ws.[c1], xlValues, , xlByRows, xlPrevious)
Dim zen As String
zen = rng1.Address(0, 0)
Range(zen).Select
Set ruby = Window.ActiveCell.Row - 11
Dim stones() As Boolean
ReDim stones(1 To ruby)
If IsEmpty(ActiveCell.Offset(2, 0)) Then
Set stones(1) = 0
Else
Set stones(1) = 1
End If
msg55 = MsgBox(stones(1), vbDefaultButton1, "Gekko")
PocketLoan
  • 534
  • 4
  • 13
  • 28

3 Answers3

2

My assumption is that you are doing this for purposes of learning rather than practicality:

You could google VBA arrays and get a plethora of material on the subject. I would start here: http://www.cpearson.com/excel/vbaarrays.htm

You would declare your array like so:

Dim stones(1 To 10) As Double

You're going to have to iterate through each cell in your range. You can Google how to do that as well:

Loop through each cell in a range of cells when given a Range object

You can set the value of the 5th element in the array to the value of 10 like so:

stones(5) = 10

It really seems like you need to do some basic VBA programming tutorials. You could start here:

http://www.mrexcel.com/forum/excel-questions/667818-tutorials-excel-macros-visual-basic-applications.html

Community
  • 1
  • 1
Stepan1010
  • 3,136
  • 1
  • 16
  • 21
  • Thanks Stepan! I edited my question now that I've gotten more direction from your response. I'm wondering why I get an object required error when setting an array element to a value... – PocketLoan Aug 19 '13 at 15:40
  • 1
    @RichardPullman Looks like you took a cursory look at how to use arrays. Beginning programming tutorials usually have an entire chapter devoted to introducing arrays. Looks like you made some progress - you changed the array type from double to boolean (which makes more sense for your situation because you're just storing 1's and 0's). - But you're really going to want to study how to use arrays. Anyone can walk you through how to use them but you are better off just reading a tutorial. You're going to want learn how to iterate. However, if you just want to get it done, you don't need arrays. – Stepan1010 Aug 19 '13 at 16:01
  • Yeah, thank you. Good point man. I should gain better understanding on a subject before I come here with questions. – PocketLoan Aug 19 '13 at 16:27
1

If you're trying to get rid of blank cells in sheet 'Belmont' column C, then this should work for you:

Sub tgr()

    Dim rngBlanks As Range

    With Sheets("Belmont").Range("C1", Sheets("Belmont").Cells(Rows.Count, "C").End(xlUp))
        On Error Resume Next
        Set rngBlanks = .SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        If Not rngBlanks Is Nothing Then rngBlanks.EntireRow.Delete
    End With

    Set rngBlanks = Nothing

End Sub
tigeravatar
  • 26,199
  • 5
  • 30
  • 38
  • More complicated than that. I want to move contents of cells to the top of the sheet to the first unoccupied column. If there are contents in rows 5,6,7, and 12, I want to move the contents of row 12 to row 8. – PocketLoan Aug 19 '13 at 15:33
  • 1
    So leave out the .EntireRow so that the line is: `If Not rngBlanks Is Nothing Then rngBlanks.Delete` – tigeravatar Aug 19 '13 at 15:39
1

If you want to delete all rows in which column C is blank, then:

Sub dural()
    Dim r As Range
    Set r = Range("C:C").Cells.SpecialCells(xlCellTypeBlanks).EntireRow
    r.Delete
End Sub

will accomplish this without looping.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99