Macro help, generating list

  • Thread starter Thread starter w-benninghoven
  • Start date Start date
W

w-benninghoven

I want to cerate a macro in Excel 2007 that does the following:
Starts at the top of a list of numbers and goes down through each,
checking the cell to the right of it to see if it is blank. If the
cell to the right is blank, takes that number (in the left column) and
puts it at the bottom of a list of previously checked numbers whoes
cell to the right is also blank.

Below is an example of what the two columns would look like. Although
there may be as many as 400 numbers in the left column.

1 a
2 a
3 a
4
6 a
11 a
14

Then the resultant list of numbers would look like this:

6
14

Any ideas?

Thanks .... Wolf-==-
 
This uses cols A & B. Adjust to suite:

Sub dural()
i = Cells(Rows.Count, "A").End(xlUp).Row
j = i + 1
For ii = 1 To i
If Cells(ii, "B").Value = "" Then
Cells(j, "A").Value = Cells(ii, "A").Value
j = j + 1
End If
Next
End Sub
 
Use data filters, and choose 'blanks' on the second column.

HTH,
Bernie
MS Excel MVP
 
Try the following macro. Just change the CheckCol (the column with your
numbers in them) and the ResultColumn (the column at the bottom of which the
checked numbers will be placed) to the actual column letters you are using.

Sub FindCheckedNumbers()
Dim R As Range
Dim Rw As Long
Const CheckCol As String = "A"
Const ResultColumn As String = "C"
Rw = Cells(Rows.Count, ResultColumn).End(xlUp).Row
For Each R In Range(Cells(1, CheckCol), Cells(Rows.Count, CheckCol). _
End(xlUp)).Offset(, 1).SpecialCells(xlCellTypeBlanks)
Rw = Rw + 1
Cells(Rw, ResultColumn).Value = R.Offset(, -1).Value
Next
End Sub
 
Try the following macro. Just change the CheckCol (the column with your
numbers in them) and the ResultColumn (the column at the bottom of which the
checked numbers will be placed) to the actual column letters you are using.

Sub FindCheckedNumbers()
  Dim R As Range
  Dim Rw As Long
  Const CheckCol As String = "A"
  Const ResultColumn As String = "C"
  Rw = Cells(Rows.Count, ResultColumn).End(xlUp).Row
  For Each R In Range(Cells(1, CheckCol), Cells(Rows.Count, CheckCol). _
                End(xlUp)).Offset(, 1).SpecialCells(xlCellTypeBlanks)
    Rw = Rw + 1
    Cells(Rw, ResultColumn).Value = R.Offset(, -1).Value
  Next
End Sub

--
Rick (MVP - Excel)










- Show quoted text -
Click to expand...

Woops, I hope you all didn't think I wanted to replace the original
column with new list of numbers that don't have the right column
associated with them, but rather I want to extract the the numbers and
place them in a new column, leaving the original set of data intact.
Thanks
 
I don't think any of them do that, but I know mine doesn't. Just specify the
columns as I indicated and try it out.

--
Rick (MVP - Excel)


Try the following macro. Just change the CheckCol (the column with your
numbers in them) and the ResultColumn (the column at the bottom of which
the
checked numbers will be placed) to the actual column letters you are
using.

Sub FindCheckedNumbers()
Dim R As Range
Dim Rw As Long
Const CheckCol As String = "A"
Const ResultColumn As String = "C"
Rw = Cells(Rows.Count, ResultColumn).End(xlUp).Row
For Each R In Range(Cells(1, CheckCol), Cells(Rows.Count, CheckCol). _
End(xlUp)).Offset(, 1).SpecialCells(xlCellTypeBlanks)
Rw = Rw + 1
Cells(Rw, ResultColumn).Value = R.Offset(, -1).Value
Next
End Sub

--
Rick (MVP - Excel)










- Show quoted text -
Click to expand...

Woops, I hope you all didn't think I wanted to replace the original
column with new list of numbers that don't have the right column
associated with them, but rather I want to extract the the numbers and
place them in a new column, leaving the original set of data intact.
Thanks
 
This uses cols A & B.  Adjust to suite:

Sub dural()
i = Cells(Rows.Count, "A").End(xlUp).Row
j = i + 1
For ii = 1 To i
    If Cells(ii, "B").Value = "" Then
        Cells(j, "A").Value = Cells(ii, "A").Value
        j = j + 1
    End If
Next
End Sub
Click to expand...

Well Gary, any other help? I tried running this and as soon as it
gets to the line starting "If Cells", generates a syntax error. Any
suggestions?
Thanks Wolf-==-
 
It works fine - just make sure that you don't have option explicit at the top of your module.

Of course, I think changing j = i + 1 to j = i + 3 would work a little better.

HTH,
Bernie
MS Excel MVP


This uses cols A & B. Adjust to suite:

Sub dural()
i = Cells(Rows.Count, "A").End(xlUp).Row
j = i + 1
For ii = 1 To i
If Cells(ii, "B").Value = "" Then
Cells(j, "A").Value = Cells(ii, "A").Value
j = j + 1
End If
Next
End Sub
Click to expand...

Well Gary, any other help? I tried running this and as soon as it
gets to the line starting "If Cells", generates a syntax error. Any
suggestions?
Thanks Wolf-==-
 
Personally, I think having Option Explicit at the top of (all) your module(s) would be better... it would just mean that all variables would have to be declared before they can be used.
 
I agree, but I was trying to think of an easy way for the OP to fix what may (or may not) be a
dimensioning error...

Bernie
MS Excel MVP


Personally, I think having Option Explicit at the top of (all) your module(s) would be better... it
would just mean that all variables would have to be declared before they can be used.
 
Back
Top