Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default Removing empty rows issue Please Help!

Hi Everyone,

I have about 10 different tabs in same format in my workbook and
having some difficulties with my macro.

Basically, My macro runs through column("B:B") and if the cell is
empty it will remove entire row, if not then it returns column D's
value with matching row.

Sub Clean()
Dim ws As Worksheet, lngRow As Long

For Each ws In Sheets

For lngRow = ws.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
If ws.Range("B" & lngRow) = "" Then
ws.Rows(lngRow).Delete
Else
ws.Range("D" & lngRow) = ws.Range("D" & lngRow)


End If

Next

Next

End Sub

How do I alter this code to return column "D", "F" and "H:AF"?


Thank you so much for your help.


Regards,


James
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Removing empty rows issue Please Help!


James;607927 Wrote:
Hi Everyone,

I have about 10 different tabs in same format in my workbook and
having some difficulties with my macro.

Basically, My macro runs through column("B:B") and if the cell is
empty it will remove entire row, if not then it returns column D's
value with matching row.

Sub Clean()
Dim ws As Worksheet, lngRow As Long

For Each ws In Sheets

For lngRow = ws.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
If ws.Range("B" & lngRow) = "" Then
ws.Rows(lngRow).Delete
Else
ws.Range("D" & lngRow) = ws.Range("D" & lngRow)


End If

Next

Next

End Sub

How do I alter this code to return column "D", "F" and "H:AF"?


Thank you so much for your help.


Regards,


James


To try to answer your question add these 2 lines after
ws.Range("D" & lngRow) = ws.Range("D" & lngRow):


Code:
--------------------
ws.Range("F" & lngRow) = ws.Range("F" & lngRow)
ws.Range("H" & lngRow & ":AF" & lngRow) = ws.Range("H" & lngRow & ":AF" & lngRow)

--------------------


but then it returns column D's value with matching row
puzzles me. The sub returns diddly-squat as far as I can tell. What is
the line
ws.Range("D" & lngRow) = ws.Range("D" & lngRow)
actually supposed to be doing?


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=168661

Microsoft Office Help

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 135
Default Removing empty rows issue Please Help!

Hi,

Try this :

'-----------------------------------
Sub Clean()
Dim ws As Worksheet, LastRow As Long

On Error Resume Next
For Each ws In Worksheets
With ws
.Range("B:B").SpecialCells(xlCellTypeBlanks).Entir eRow.Delete
LastRow = .Range("B" & .Cells.Rows.Count).End(xlUp).Row
With .Range("D1:F" & LastRow)
.Value = .Value
End With
With .Range("H1:AF" & LastRow)
.Value = .Value
End With
End With
Next
End Sub
'-----------------------------------



"James" a écrit dans le message de groupe de discussion :
...
Hi Everyone,

I have about 10 different tabs in same format in my workbook and
having some difficulties with my macro.

Basically, My macro runs through column("B:B") and if the cell is
empty it will remove entire row, if not then it returns column D's
value with matching row.

Sub Clean()
Dim ws As Worksheet, lngRow As Long

For Each ws In Sheets

For lngRow = ws.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
If ws.Range("B" & lngRow) = "" Then
ws.Rows(lngRow).Delete
Else
ws.Range("D" & lngRow) = ws.Range("D" & lngRow)


End If

Next

Next

End Sub

How do I alter this code to return column "D", "F" and "H:AF"?


Thank you so much for your help.


Regards,


James

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default Removing empty rows issue Please Help!

If I know that the last row will always be B2200, how do I alter the
code? will it run significantly faster?





On Jan 11, 12:21*pm, "michdenis" wrote:
Hi,

Try this :

'-----------------------------------
Sub Clean()
Dim ws As Worksheet, LastRow As Long

On Error Resume Next
For Each ws In Worksheets
* * With ws
* * * * .Range("B:B").SpecialCells(xlCellTypeBlanks).Entir eRow.Delete
* * * * LastRow = .Range("B" & .Cells.Rows.Count).End(xlUp).Row
* * * * With .Range("D1:F" & LastRow)
* * * * * * .Value = .Value
* * * * End With
* * * * With .Range("H1:AF" & LastRow)
* * * * * * .Value = .Value
* * * * End With
* * *End With
Next
End Sub
'-----------------------------------

"James" a écrit dans le message de groupe de discussion :
...
Hi Everyone,

I have about 10 different tabs in same format in my workbook and
having some difficulties with my macro.

Basically, My macro runs through column("B:B") and if the cell is
empty it will remove entire row, if not then it returns column D's
value with matching row.

Sub Clean()
Dim ws As Worksheet, lngRow As Long

For Each ws In Sheets

* * For lngRow = ws.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
* * *If ws.Range("B" & lngRow) = "" Then
* * * * *ws.Rows(lngRow).Delete
* * Else
* * * * ws.Range("D" & lngRow) = ws.Range("D" & lngRow)

* * End If

Next

Next

End Sub

How do I alter this code to return column "D", "F" and "H:AF"?

Thank you so much for your help.

Regards,

James


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 135
Default Removing empty rows issue Please Help!

| will it run significantly faster?

No. Nothing noticeable

But if you want to adapt, you could do this :

'----------------------------------
Sub Clean()
Dim ws As Worksheet

On Error Resume Next
For Each ws In Worksheets
With ws
.Range("B1:B2200").SpecialCells(xlCellTypeBlanks). EntireRow.Delete
With .Range("D1:F2200")
.Value = .Value
End With
With .Range("H1:AF2200")
.Value = .Value
End With
End With
Next
End Sub
'----------------------------------



"James" a écrit dans le message de groupe de discussion :
...
If I know that the last row will always be B2200, how do I alter the
code? will it run significantly faster?





On Jan 11, 12:21 pm, "michdenis" wrote:
Hi,

Try this :

'-----------------------------------
Sub Clean()
Dim ws As Worksheet, LastRow As Long

On Error Resume Next
For Each ws In Worksheets
With ws
.Range("B:B").SpecialCells(xlCellTypeBlanks).Entir eRow.Delete
LastRow = .Range("B" & .Cells.Rows.Count).End(xlUp).Row
With .Range("D1:F" & LastRow)
.Value = .Value
End With
With .Range("H1:AF" & LastRow)
.Value = .Value
End With
End With
Next
End Sub
'-----------------------------------

"James" a écrit dans le message de groupe de discussion :
...
Hi Everyone,

I have about 10 different tabs in same format in my workbook and
having some difficulties with my macro.

Basically, My macro runs through column("B:B") and if the cell is
empty it will remove entire row, if not then it returns column D's
value with matching row.

Sub Clean()
Dim ws As Worksheet, lngRow As Long

For Each ws In Sheets

For lngRow = ws.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
If ws.Range("B" & lngRow) = "" Then
ws.Rows(lngRow).Delete
Else
ws.Range("D" & lngRow) = ws.Range("D" & lngRow)

End If

Next

Next

End Sub

How do I alter this code to return column "D", "F" and "H:AF"?

Thank you so much for your help.

Regards,

James


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Removing empty cells rehaylock Excel Discussion (Misc queries) 1 May 9th 08 07:29 PM
Removing Empty Rows from Worksheets Gareth - Network analyst. Excel Discussion (Misc queries) 2 August 8th 07 09:59 PM
Delete Rows with Empty Cells with empty column 1 Scott Excel Programming 5 October 2nd 06 11:57 PM
Finding criteria and removing matching rows (Range issue?) Ronny Hamida Excel Programming 2 April 13th 06 04:20 PM
Removing Empty Rows and selecting Specific Rows Jetheat[_8_] Excel Programming 7 August 12th 05 12:10 AM


All times are GMT +1. The time now is 07:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"