LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,101
Default JLatham MVP (Macro)

Could You Look over And Maybe Clean up
I have Store Nuber in (B2) And If SOD in I3 is 0 it will move row 2 as well

And MIDC is the Warehouse name

Sub MIDC()
Const SOD = "I3"
Dim EmptyRow As Long
Dim MovedCount As Long
Dim LC As Long


EmptyRow = Range("I" & Rows.Count).End(xlUp).Row + 3
Application.ScreenUpdating = False
Do Until (MovedCount + LC) = EmptyRow
If Range(SOD).Offset(LC, 0) = 0 Then
Rows(Range(SOD).Offset(LC, 0).Row & _
":" & Range(SOD).Offset(LC, 0).Row).Copy
Rows(EmptyRow & ":" & EmptyRow).Select
ActiveSheet.Paste
Rows(Range(SOD).Offset(LC, 0).Row & ":" & _
Range(SOD).Offset(LC, 0).Row).Delete Shift:=xlUp
MovedCount = MovedCount + 3
LC = LC - 1
Else
LC = LC + 1
End If
Loop
Range(SOD).Select
Application.ScreenUpdating = True
Dim LastRowUsed As Long
Dim TestValue As Long


LastRowUsed = Range("C" & Rows.Count).End(xlUp).Row
TestValue = 19999
Range("C4").Select
Application.ScreenUpdating = False
Do Until TestValue 99999
If ActiveCell.Offset(-1, 0) <= TestValue And _
ActiveCell.Value TestValue Then

Selection.EntireRow.Insert
LastRowUsed = LastRowUsed + 1
TestValue = TestValue + 10000
End If
ActiveCell.Offset(1, 0).Activate
If ActiveCell.Row LastRowUsed Then
Exit Do
End If
Loop

Columns("B:I").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

End Sub
 
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
how do I email amacro? leo Excel Worksheet Functions 24 August 9th 06 02:47 PM
error when running cut & paste macro Otto Moehrbach Excel Worksheet Functions 4 August 9th 06 01:49 PM
Search, Copy, Paste Macro in Excel [email protected] Excel Worksheet Functions 0 January 3rd 06 06:51 PM
Closing File Error jcliquidtension Excel Discussion (Misc queries) 4 October 20th 05 12:22 PM
Highlight Range - wrong macro, please edit. Danny Excel Worksheet Functions 8 October 19th 05 11:11 PM


All times are GMT +1. The time now is 12:29 AM.

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

About Us

"It's about Microsoft Excel"