PDA

View Full Version : Two issues w/ macro



YellowLabPro
09-21-2006, 05:29 AM
Good Morning,
I am experimenting and have hit a bug / issue regarding two items in this macro:

1) The Set statement is a problem
2) The If Statment is supposed to delete any rows that has no value in column A. But it fails to do so.


Sub Lesson5_1()
Dim LRow As Long
Dim ws8 As Worksheet
'Dim x&
'Dim Count&
Set ws8 = ("Sheet8")
LRow = ws8.Cells(Rows.Count, "a").End(xlUp).Row
If ws8.Range("A1" & LRow).Value = "" Then Range("a" & LRow).EntireRow.Delete

' For x = 1 To 1500
' Sheet8.Range("A" & x).Value = x
' Next
'
End Sub


Thanks

YLP

johnske
09-21-2006, 05:37 AM
Sub Lesson5_1()
Dim LRow As Long
Dim ws8 As Worksheet
'Dim x&
'Dim Count&
Set ws8 = Sheets("Sheet8")
LRow = ws8.Cells(Rows.Count, "a").End(xlUp).Row
If ws8.Range("A1" & LRow).Value = "" Then Range("a" & LRow).EntireRow.Delete

' For x = 1 To 1500
' Sheet8.Range("A" & x).Value = x
' Next
'
End Subuse Sheets("Sheet8") or Worksheets("Sheet8"). It's deleting it, you just don't see it - try colouring the row then run the procedure

johnske
09-21-2006, 05:51 AM
BTW yelp, the loop you've commented out is the slowest way to do it - try this... With Sheet8.Range("A1:A1500")
.Formula = "=ROW(B1)"
.Value = .Value '< erase the formula
End With

YellowLabPro
09-21-2006, 06:14 AM
Thanks John,
Part 1- I am looking to delete the entire row, but the row remains. I did follow your instructions and colored the cell of a blank cell in column A and it still remains, did not delete the row.

Part 2
This is just an excerpt from a lesson-- I am just looking to the way things work right now, not speed.... yet :-)

Thanks

YLP

johnske
09-21-2006, 06:39 AM
ok, look at the line where you're finding the last row (LRow)... now let's suppose the last row is row 10, so Lrow = 10.

in the next line you have If ws8.Range("A1" & LRow).Value = "" Then Range("a" & LRow).EntireRow.Deletebecause you have "A1" & LRow you are concatenating the A1 with 10 - which gives A110. As A110 is always definitely empty (""), then the last row (row 10) is deleted - which is exactly what your code is telling Visual Basic to do and what's happening on my machine :)

YellowLabPro
09-21-2006, 10:55 AM
I have not figure out how to alter my code to delete rows that are blank in the column from A1 to LRow-- but I do see the results and the point you made now. Thanks for pointing this out.

YLP

YellowLabPro
09-21-2006, 11:20 AM
Slightly altered code is included, not significant-- But still not able to delete all the empty cells in the range. My data spans from A1 to A1500 and A9 and A17 are empty, which I would like to delete these two rows.


Sub Lesson5_1()
Dim LRow As Long
Dim ws8 As Worksheet
Dim x&
'Dim Count&
Set ws8 = Sheets("Sheet8")
LRow = ws8.Cells(Rows.Count, "a").End(xlUp).Row
If ws8.Range("A" & LRow).Value = "" Then Range("a" & LRow).EntireRow.Delete

' For x = 1 To 1500
' Sheet8.Range("A" & x).Value = x
' Next
End Sub


Thanks,

YLP

johnske
09-21-2006, 02:14 PM
Seeing it's a lesson... there's several ways you can go about this, so here's some things to look at and experiment with (choose what you're familiar with).
a) A loop with an index number - slow
b) A For_Each loop - faster
c) SpecialCells(xlCellTypeBlanks) - fastest

YellowLabPro
09-21-2006, 02:22 PM
Ok, I will do that. But the thing that is tripping me up is, if I am looking at the range, the range being in "A" to the last row in "A", why does this not work to delete the empty rows?
Sorry if this should be easier to get and I am not doing so...
I have never looked at concatenating as the way this works. The way I thought it was working was it started in A, went down to the last row in the column and then back up to the first non-blank cell and then progressed up A.
But in your explanation, you show it as joining the two, which is really throwing a curve ball my way. And please note this is my lack of understanding.... :-)

johnske
09-21-2006, 02:54 PM
the lineIf ws8.Range("A" & LRow).Value = "" Then Range("a" & LRow).EntireRow.Deleteis saying "if the bottom-most data entry in column A is equal to "" then delete the row.

However (with the exception of a completely empty sheet) the last cell is never equal to "" so nothing happens.

note that here you are only telling visual basic to look at the last cell in the column - nothing else.

YellowLabPro
09-23-2006, 06:52 AM
Ok, that makes perfect sense to me now. But in still trying to write a line of code to look for any row that is blank in "A" and delete that row is erroring out.
Here is where I am so far:


Sub Lesson5_1b()
Dim LRow As Long
Dim ws8 As Worksheet
Dim x&
Dim Count&
Set ws8 = Sheets("Sheet8")
For x = 1 To 1500
Sheet8.Range("A" & x).Value = x
Next

LRow = ws8.Cells(Rows.Count, "a").End(xlUp).Row
If ws8.Range("a1:a" & LRow).Value = "" Then Range("a1:a" & LRow).EntireRow.Delete

End Sub



Your help is much appreciated--

YLP

lucas
09-23-2006, 07:10 AM
Hi YLP,
I would suggest using Option explicit at the top of your modules...you will find that you have variables not defined. It also looks like you are deleting a row but its the last row in the range which is blank?! I may be off base here but thats how it appears to be working.

mdmackillop
09-23-2006, 07:17 AM
Hi Yelp
Sheet8.Range("A" & x).Value = x
'should read
ws8 .Range("A" & x).Value = x

This code will write 1 to 1500 in successive cells, so there are no rows to be deleted. Add Step 2 to create blank rows.

If ws8.Range("a1:a" & LRow).Value = ""

You cannot test a range for blanks in this way. You must either loop through each cell or use the SpecialCells method. I would recommend the latter.

Sub Lesson5_1b()
Dim LRow As Long
Dim ws8 As Worksheet
Dim x&
Dim Count&
Set ws8 = Sheets("Sheet8")
For x = 1 To 1500 Step 2
ws8.Range("A" & x).Value = x
Next

ws8.Range(Cells(1, 1), Cells(Rows.Count, _
"a").End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

lucas
09-23-2006, 07:17 AM
See Johns post #10 it is apparently just looking but not deleting....
there are easy solutions to this but since its a lesson you should try your ideas and post more questions.

YellowLabPro
09-23-2006, 07:20 AM
Hi Lucas,
I am using the option explicit, there are a series of procedures here, I just did not copy it to the board here. I think I have all the variables defined.

Secondly, you are on base. That is what John has been saying. I am trying to figure out how to change the code to acommodate my desired results. This is based off a lesson, not an actual task, so I am just muddling around.
I am trying to look for the last row in A and then Up and delete blank rows. But as you say, it is performing this on just the last row. I thought by adding the range a1:a & Lrow, this would do that.

YellowLabPro
09-23-2006, 07:21 AM
Hi Malcolm,
Yep, you are right, I am going back in and deleting random rows manually to accommodate this.

YellowLabPro
09-23-2006, 07:24 AM
Malcolm,
Thanks again, John advised of the same thing. I think I got stuck on one thing and blocked out his advice unintentionally.

I am going to also try looping through each cell, this is what the lesson is based on--

Thanks

YellowLabPro
09-23-2006, 07:24 AM
I will do that, thanks Lucas

lucas
09-23-2006, 07:30 AM
Yelp,
I had commented out this code and created a couple of lines with blanks but this line of code as Malcolm points out is the reason I suggested the "Option Explicit"
For x = 1 To 1500
Sheet8.Range("A" & x).Value = x
Next

it should be changed as Malcolm shows. He also gives you a very good hint(hmm, little better than a hint) on how to find the blank rows from the bottom up in his post.

mdmackillop
09-23-2006, 07:30 AM
A looping solution, but only if you get stuck!

Sub Lesson5_1b()
Dim LRow As Long
Dim ws8 As Worksheet
Dim x&
Dim Count&
Set ws8 = Sheets("Sheet8")
For x = 1 To 1500 Step 2
ws8.Range("A" & x).Value = x
Next
LRow = ws8.Cells(Rows.Count, "a").End(xlUp).Row
For x = LRow To 1 Step -1
If ws8.Range("a" & x).Value = "" Then
Range("a" & x).EntireRow.Delete
End If
Next
End Sub

YellowLabPro
09-23-2006, 07:54 AM
Lucas--
I did not see any where you had posted code w/ commented out code.... am I missing something. Also regarding that section, I have it commented out and then put back in and then manually delete a couple of cells to play with it... so we are cool there, I just am wondering if you posted other code that might be helpful and I am not privy to it....
Malcolm's code in his first and second post does solve it, I will do what I do and then after either succeeding or getting stuck, go and look at his approach-- the answers in the back of the book are so tempting.... LOL-- but "No pain-- No Gain" and as much pain as I have right now- I am looking at Rock Star status.

Thanks for the advice

Malcolm,
Thanks....

YLP

lucas
09-23-2006, 11:22 AM
Hi Yelp,
No, I was just doing the same as you...commented out the code that created the numbers in col. A...same reason.

Its better to learn it than be like me..copy & paste.