Working with Arrays VBA Excel

Question

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")

Community · Accepted Answer · 2018-05-08 21:48:18Z

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

edited May 8, 2018 at 21:48

CommunityBot

11 silver badge

answered Aug 19, 2013 at 15:08

Stepan1010

3,1341 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

PocketLoan Over a year ago

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...

Stepan1010 Over a year ago

@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.

PocketLoan Over a year ago

Yeah, thank you. Good point man. I should gain better understanding on a subject before I come here with questions.

tigeravatar · Accepted Answer · 2013-08-19 15:13:26Z

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

answered Aug 19, 2013 at 15:13

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

2 Comments

PocketLoan Over a year ago

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.

tigeravatar Over a year ago

So leave out the .EntireRow so that the line is: If Not rngBlanks Is Nothing Then rngBlanks.Delete

Gary's Student · Accepted Answer · 2017-11-08 19:53:21Z

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.

edited Nov 8, 2017 at 19:53

answered Aug 19, 2013 at 15:21

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

Working with Arrays VBA Excel

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related